Geek Answers Handbook

You cannot specify the destination table "NAME" for updating in the FROM clause

I am trying to apply this solution to my data in a table in a MySQL database that has duplicates. And I get this 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)

I also tried assigning aliases - but to no avail. What is the cause of this error here? Usually it indicates that the SQL script can generate a looping process, but here I actually do not see any relation to this. Obviously, the two choices for DELETE and for SELECT are separated - the engine must make SELECT once in the first place and then use it in the WHERE for DELETE . So - why is this error happening and how can I actually deduplicate my table? =)

+8
sql mysql
source share
5 answers

try this might help you

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

More details

+27
source share

Your query is correct and will work on other DBMSs, but MySQL does not 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 may be fixed for future releases, but your request is currently not supported.

A simple solution would be to put your subquery in a subquery, as in echo_Me's 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 are not actually selecting the same table, but from the temporary table, this query will work fine. However, performance may be poor.

You usually get rid of error # 1093 using unions. This is your request in the form of a connection:

 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:

 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 here .

+4
source share

From the manual :
"Currently, you cannot delete from a table and select from the same table in a subquery."

Use the appropriate database engine or run the queries separately. (Saving id programmatically, in between.)

+3
source share

If you want to delete records from a table, use DELETE FROM tablename . You cannot specify a column here. If you want to remove data from a column, use the UPDATE statement to set the column value to empty or NULL.

0
source share

sintax removal

no column name required

as mysql deletes all data satisfying the condition

-one
source share

More articles:


All Articles