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

Answer 1

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

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 was not declared as an alias. Try t everywhere you have t1 (or vice versa).