DELETE + JOIN + ORDER BY + LIMIT = syntax error

Question

Drop ORDER BY + LIMIT , or JOIN , and all that peaches. Combine them and I seem to release the Kraken. Who can shed 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 also tried this without aliases and dropped WHERE , but to no avail. Always the syntax error is " near 'ORDER BY... ".

Answer 1

From Mysql Docs: DELETE

For syntax with multiple tables, DELETE removes lines from each tbl_name 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 

Answer 2

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) 

Answer 3

t1 not declared as an alias. Try t wherever t1 (or vice versa).