DELETE + JOIN + ORDER BY + LIMIT = syntax error

Question

Drop the ORDER BY + LIMIT, or the JOIN, and everything is peaches. Put them together and I seem to release the Kraken. Anyone who can shed some light?

DELETE table1 AS t1
FROM table1 AS t1 LEFT JOIN table2 AS t2 ON t1.id = t2.id
WHERE t2.field = 'something'
ORDER BY t1.id DESC
LIMIT 5

(Delete using aliases)

I've also tried it without aliases & dropping the WHERE, to no avail. Always a syntax error "near 'ORDER BY...".

ypercubeᵀᴹ · Accepted Answer · 2011-07-29T22:51:07.017

11

From Mysql Docs: DELETE

For the multiple-table syntax, DELETE deletes from each tbl_name the rows that satisfy the conditions. In this case, ORDER BY and LIMIT cannot be used.

In your case, I think this works:

DELETE 
FROM table1
WHERE EXISTS
      ( SELECT t2.id
        FROM table2 AS t2
        WHERE t2.id = table1.id
          AND t2.field = 'something'
      ) 
ORDER BY id DESC
LIMIT 5

edited Jul 29 '11 at 22:51

answered Jul 29 '11 at 22:35

ypercubeᵀᴹ

113,259
19
174
235

2

So in layman's terms, I can have pie *or* cool whip, but not both. Blast. – TheDeadMedic Jul 29 '11 at 22:39
See my update. A subquery with `IN` would work too. But usually `EXISTS` can be better optimized than an `IN` subquery in MySQL. – ypercubeᵀᴹ Jul 29 '11 at 22:52

score 3 · Answer 2 · answered Jul 29 '11 at 22:43

If you really need to do this you can do the following

DELETE table1 
WHERE id in
      (SELECT t.id
      FROM table1 AS t INNER JOIN table2 AS t2 ON t1.id = t2.id
      WHERE t2.field = 'something' --No point in doing a LEFT JOIN because of this
      ORDER BY t1.id DESC
       LIMIT 5)

score 1 · Answer 3 · answered Jul 29 '11 at 22:23

1

t1 was not declared as an alias. Try t everywhere you have t1 (or vice versa).

answered Jul 29 '11 at 22:23

Justin M. Keyes

6,679
3
33
60

DELETE + JOIN + ORDER BY + LIMIT = syntax error

3 Answers3

Linked