Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 306
TheDeadMedic Avatar asked Jul 29 '11 22:07

TheDeadMedic


People also ask

Can we use LIMIT in delete query?

You can specify multiple tables in a DELETE statement to delete rows from one or more tables depending on the condition in the WHERE clause. You cannot use ORDER BY or LIMIT in a multiple-table DELETE .

Is delete allowed in JOIN?

It is totally possible to use JOIN and multiple tables in the DELETE statement.

What is delete join?

DELETE JOIN is an advanced structured query language(SQL) statement that is used to perform delete operations in multiple tables while using SQL JOIN such that all rows are deleted from the first table and the matching rows in another table or based on the kind of join operation used in the query.


1 Answers

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
like image 180
ypercubeᵀᴹ Avatar answered Sep 19 '22 13:09

ypercubeᵀᴹ