8

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? =)

Community
  • 1
  • 1
Gill Bates
  • 14,330
  • 23
  • 70
  • 138
  • 1
    Your query looks nothing like the one from the solution that you're trying to apply. They use a join, whereas you're using `NOT IN`. – eggyal Jul 19 '13 at 09:11
  • @eggyal My link points to the comment, not to the answer – Gill Bates Jul 19 '13 at 09:17
  • Ah, fair enough; I hadn't noticed that. That comment is SQL Server specific. You will find the join given in the answer will work in MySQL. – eggyal Jul 19 '13 at 09:19

5 Answers5

28

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

Techie
  • 44,706
  • 42
  • 157
  • 243
echo_Me
  • 37,078
  • 5
  • 58
  • 78
  • But actually I dont understand - if docs says that you cant do subqueries in DELETE (see aib answer) - why your solution works? – Gill Bates Jul 19 '13 at 09:45
  • 2
    its discussed [here](http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause/45498#45498) – echo_Me Jul 19 '13 at 09:47
  • It worked but why we cant specify what we are updating in from. I started learning SQL from [here](sql.learncodethehardway.org/book/ex8.html). Here they used it in exercise8 and exercise10. Please check. – paul Apr 18 '14 at 06:50
4

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.

fthiella
  • 48,073
  • 15
  • 90
  • 106
3

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

Use a proper database engine, or run the queries separately. (Storing the id's programmatically, in between.)

aib
  • 45,516
  • 10
  • 73
  • 79
0

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

Biju P
  • 98
  • 3
-1

the sintax of delete

not require the name of columns

because mysql delete all data that meet a condition

bomberdini
  • 9
  • 2