mysql: ERROR 1093 - You can't specify target table 'foo' for update in FROM clause
This is a wiki page. Be bold and improve it!
If you have any questions about the content on this page, don't hesitate to open a new ticket and we'll do our best to assist you.
UPDATE `foo` SET item_value = ( SELECT item_value FROM `foo` WHERE item_id = 3) WHERE item_id = 2896
Generated the following error:
#1093 - You can't specify target table 'foo' for update in FROM clause
You cannot change a table (DELETE or UPDATE) and SELECT from it at the same time. Given the way mysql treats sub-queries, there is no guarantee of the state the table will be during the SELECT stage.
Instead, use two queries: one to SELECT the value, the second to UPDATE it.
If you know what you are doing, you can also try with a JOIN statement on the same table:
UPDATE `foo` AS f1
INNER JOIN (
SELECT item_value FROM `foo` WHERE item_id = 3
) AS f2
SET f1.item_value = t2.item_value
WHERE item_id = 2896
Depending on the exact nature of the query, and on the optimizations done in the background by the sql engine, this may or may not work. Beware!