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...
".
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 .
It is totally possible to use JOIN and multiple tables in the DELETE statement.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With