CODEWITHSUNDEEP
mysql
Ryan
Ryan

Reputation: 1043

MySQL: You can't specify target table 'list' for update in FROM clause

I'm attempting to run the following query:

update list
set li = '6'
where li = '5'
and dn in ( SELECT dn FROM list GROUP BY dn HAVING COUNT(*) < 2000 )

This query results in the error mentioned above:

You can't specify target table 'list' for update in FROM clause

So I got advice from someone who advised me to run the following:

START TRANSACTION;
UPDATE  list a
    INNER JOIN
    (
        SELECT  dn 
        FROM    list 
        GROUP   BY dn 
        HAVING  COUNT(*) < 2000 
    ) b ON a.dn = b.dn
SET     a.li = '6'
WHERE   a.li = '5';

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.

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

Can anyone guide me to a solution that will allow me to update these records? Much appreciated.

Upvotes: 0

Views: 634

Answers (3)

Miklos Aubert
Miklos Aubert

Reputation: 4585

This might work, so, continuing from my comment above, here's an answer :

Yes, remove the quotes around '6' and '5', you don't want MySQL to cast the li column to a string on every comparison... and do create an index.

CREATE idx_dn_li ON list(dn, li);

UPDATE  list a
    INNER JOIN
    (
        SELECT  dn 
        FROM    list 
        GROUP   BY dn 
        HAVING  COUNT(*) < 2000 
    ) b ON a.dn = b.dn
SET     a.li = 6
WHERE   a.li = 5;

The index will make your GROUP BY faster, and I think having the li column in there can also help (as I see it, in the case where no records are affected, your query should only ever read the index, but don't take my word for it ;) )

Upvotes: 2

Justin
Justin

Reputation: 9724

Query:

update list
set li = '6'
where li = '5'
and dn in ( SELECT a.dn 
            FROM (SELECT * FROM list) a 
            GROUP BY a.dn 
            HAVING COUNT(*) < 2000 )

This query probably will be much more faster:

UPDATE list t
SET t.li = '6'
WHERE t.li = '5'
AND (SELECT COUNT(*)
     FROM (SELECT * FROM tik) a 
     WHERE a.dn = t.dn) < 2000

Upvotes: 0

Hiren Pandya
Hiren Pandya

Reputation: 980

This error is generated when we are trying to update a table and we are also having the same table in another query to update a column.

We can't select from a table you're updating at the same time. However, we can do multi-table updates like

UPDATE tbl AS a
  INNER JOIN tbl AS b ON ....
  SET a.col = b.col

Or try the simplified version of your query

update list l1, ( SELECT dn FROM list GROUP BY dn HAVING COUNT(*) < 2000 ) AS l2
set li = '6'
where li = '5'
and dn = l2.dn

Hope it helps.. Here is the reference for you... http://7php.com/mysql-update-select-statement-1093/

Upvotes: 0

Related Questions