update…where id in (select…) and mySQL ERROR 1093
20080826 17:25 by javierWhat’s wrong with this picture?
mysql> UPDATE IGNORE flight SET intl=1 WHERE id IN (SELECT f.id AS id FROM flight f LEFT JOIN airport a ON a.id=f.origin WHERE a.country<>‘mx’ FOR UPDATE);
ERROR 1093 (HY000): You can‘t specify target table ‘flight‘ for update in FROM clause
ERROR 1093 (HY000): You can‘t specify target table ‘flight‘ for update in FROM clause
This construct is invalid in mySQL
According to the manual,
You can use a subquery for assignment within an UPDATE statement because subqueries are legal in UPDATE and DELETE statements as well as in SELECT statements. However, you cannot use the same table (in this case, table t1) for both the subquery’s FROM clause and the update target.
The equivalent multi-table update does the same and works as intended:
UPDATE flight f LEFT JOIN airport a ON a.id=f.origin SET f.intl=1 WHERE a.country<>‘mx’;
