Search code examples
mysqldelete-row

DELETE + JOIN + ORDER BY + LIMIT = syntax error

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

Solution

  • 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