MySQL delete with subquery gives syntax error

Question

In MySQL I have a table ('ertek') with 2 fields: 'id': int, autoincrement 'mert': int

I'd like to delete rows where the value of 'mert' is lower than in the previous row, if I order the table by id ASC.

My query:

DELETE FROM ertek as t WHERE EXISTS (SELECT * FROM ertek AS t2 WHERE t2.id=t.id-1 AND t2.mert>t.mert)

I receive this error: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as t WHERE EXISTS (SELECT * FROM ertek AS t2 WHERE t2.id=(t.id)-1 AND t2.mert>t' at line 1

Please help.

possible duplicate of [DELETE FROM \`table\` AS \`alias\` ... WHERE \`alias\`.\`column\` ... why syntax error?](http://stackoverflow.com/questions/10484532/delete-from-table-as-alias-where-alias-column-why-syntax-error) — Sadikhasan, Jul 09 '14 at 10:40

score 0 · Accepted Answer · answered Jul 09 '14 at 10:36

0

Try without the alias for the table name that is deleted

DELETE FROM ertek D
WHERE EXISTS 
(SELECT * FROM ertek AS t2 WHERE t2.id=D.id-1 AND t2.mert>D.mert)

or

  DELETE d1 FROM ertek d1 JOIN ertek d2 
  ON d2.id= (d1.id-1) AND d1.mert< d2.mert;

answered Jul 09 '14 at 10:36

ngrashia

9,869
5
43
58

Reference: http://stackoverflow.com/questions/3346068/delete-statement-in-a-same-table – ngrashia Jul 09 '14 at 10:41

MySQL delete with subquery gives syntax error

1 Answers1