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!