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

Question

Im trying to apply this solution to my data in table in MySQL DB which has duplicates. And I get such error:

SQL Error [1093] [HY000]: You can't specify target table 'NAME' for update in FROM clause


DELETE NAME FROM NAME
WHERE NAME.id NOT IN
   (SELECT MIN(id) 
   FROM NAME GROUP BY col1, col2)

Also tried too assign aliases - but without success. What reason of this error here? It generally points that SQL script can produce cyclic process, but here I actually dont see any relevant to this - It is obvious that two selections for DELETE and for SELECT is detached - engine must do SELECT once firstly and then use it in WHERE conditions for DELETE. So - why this error happens and how can I actually deduplicate my table? =)

Answer 1

try this may help you

DELETE  FROM NAME
WHERE NAME.id NOT IN (
                     SELECT * FROM ( 
                                    SELECT MIN(id)  FROM NAME GROUP BY col1,col2
                                   ) AS p 
                      ) 

Read more

Answer 2

Your query is correct and would work on other DBMS, but MySQL doesn't allow you to update or delete from a table and select from the same table in a subquery. It is documented on the official DELETE docs.

It might be fixed on future releases, but currently your query is not supported.

A simple fix would be to put your subquery in a sub-subquery, like in echo_Me answer:

WHERE NAME.id NOT IN (SELECT * FROM (your subquery) s)

this will force MySQL to create a temporary table with the results of your subquery, and since you actually are not selecting from the same table, but from a temporary table, this query will run fine. However, performances might be poor.

You usually get rid of error #1093 by using joins. This is your query in join form:

DELETE NAME
FROM
  NAME LEFT JOIN (
    SELECT col1, col2, MIN(id) min_id 
    FROM NAME
    GROUP BY col1, col2) s
  ON (NAME.col1, NAME.col2, NAME.id) = (s.col1, s.col2, s.min_id)
WHERE
  s.min_id IS NULL

or you can use this simpler version, which should be the fastest one:

DELETE N1
FROM
  NAME N1 INNER JOIN NAME N2
  ON
    N1.col1=N2.COL1
    AND N1.col2=N2.col2
    AND N1.ID > N2.ID

Fiddle is here.