Category Archives: MySQL

You can't specify target table 'xxx' for update in FROM clause

On MySQL, you cannot use the same table for both the subquery FROM clause and the update target.

Source: MySQL :: MySQL 5.1 Reference Manual :: 13.2.9.9 Subquery Errors

Example:

mysql> delete from user \
    -> where user.id in \
    -> (select user.id from user \
    -> left join reserve_data on user.id = reserve_data.user_id where reserve_data.id is null);
ERROR 1093 (HY000): You can't specify target table 'user' for update in FROM clause

Workaround

mysql> delete from user \
    -> where user.id in \
    -> (select x.id from \
    -> (select user.id from user left join reserve_data on user.id = reserve_data.user_id where reserve_data.id is null) \
    -> as x \
    -> );
Query OK, 350 rows affected (0.00 sec)

In the above example, the subquery creates an implicit temporary table, so it doesn't count as the same table you're updating.

Source: SQL Delete: can't specify target table for update in FROM clause - Stack Overflow