title: MySQL: You can't specify target table 'list' for update in FROM clause
body: <p>I'm attempting to run the following query:</p>
  
  <pre><code>update list
  set li = '6'
  where li = '5'
  and dn in ( SELECT dn FROM list GROUP BY dn HAVING COUNT(*) &lt; 2000 )
  </code></pre>
  
  <p>This query results in the error mentioned above:</p>
  
  <pre><code>You can't specify target table 'list' for update in FROM clause
  </code></pre>
  
  <p>So I got advice from someone who advised me to run the following:</p>
  
  <pre><code>START TRANSACTION;
  UPDATE  list a
      INNER JOIN
      (
          SELECT  dn 
          FROM    list 
          GROUP   BY dn 
          HAVING  COUNT(*) &lt; 2000 
      ) b ON a.dn = b.dn
  SET     a.li = '6'
  WHERE   a.li = '5';
  </code></pre>
  
  <p>This code runs - problem is it never finishes.  I'm not sure if it's a problem with the statement itself or if it's because of the 4million records it's trying to work itself through, but I left it going last night when I went to bed and it hadn't finished this morning.  Also, just an afterthought:  I did try to roll this into a transaction in case things went fugly so threw in a 'START TRANSACTION;' at the start of this.</p>
  
  <p>I get the impression this is a fairly common question on here - but I'm not SQL savvy enough to take someone elses query and adjust it to fit my own needs, unfortunately. :p</p>
  
  <p>Can anyone guide me to a solution that will allow me to update these records?  Much appreciated.</p>