I had an issue with a MySQL query containing a sub-query recently where it worked fine when done as a
SELECT query, but gave an error when switching it to a
DELETE query. The error given was something like ‘You can’t specify target table “items” for update in FROM clause’. The sub-query was referencing the same table as the main query, which apparently can’t be done directly in MySQL because the table will be modified during deletion. But there is a sort of a hack I found in this StackOverflow answer, among others, to force it to create a temp table and allow it to work.
The query selected items with some conditions including being the only item in a given list, that being where the sub-query came in. The original query looked something like this:
SELECT *, ( SELECT COUNT(i2.id) FROM items i2 WHERE i2.list_id = il.id ) AS icnt FROM items i LEFT JOIN item_lists il ON il.id = i.list_id WHERE i.condition = 'value' AND ( SELECT COUNT(i3.id) FROM items i3 WHERE i3.list_id = il.id ) = 1
Just changing the
SElECT part to
DELETE i gave the “target table” error. To make it work as a
DELETE query, I had to modify the sub-query to have its own nested sub-query. This is what forces the temp table to be created. The inner sub-query cannot reference tables from the main query though, so I had to put that part in the outer sub-query. The end result looked something like this:
DELETE i FROM items i LEFT JOIN item_lists il ON il.id = i.list_id WHERE i.condition = 'value' AND ( SELECT COUNT(i3.id) FROM ( SELECT id, list_id FROM items i4 ) AS i3 WHERE i3.list_id = il.id ) = 1
It did what I wanted. This would also have to be done for an
UPDATE and presumably
INSERT query. Hopefully I’ll be able to remember this the next time I encounter it.