update…where id in (select…) and mySQL ERROR 1093

What’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

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’;

2 thoughts on “update…where id in (select…) and mySQL ERROR 1093”

  1. gracias yo se q alguien tendria q decir algo razonable para resolverlo

Comments are closed.