read more at Не люблю проприетарщину/покажи свои сорцы

mysql> create table t (a bigint primary key, b bigint);
Query OK, 0 rows affected (0.11 sec)
 
mysql> insert into t values (1,1);
Query OK, 1 row affected (0.08 sec)
 
mysql> insert into t values (2,3);
Query OK, 1 row affected (0.02 sec)
 
mysql> update t set b = null where b not in (select a from t);
ERROR 1093 (HY000): You can't specify target table 't' for update in FROM clause

ripos

mysql> update t set b = null where a in
  (select a from (select a from t where b not in (select a from t)) as temp);
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select * from t;
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 2 | NULL |
+---+------+
2 rows in set (0.00 sec)

read more at Не люблю проприетарщину/покажи свои сорцы

read more at Не люблю проприетарщину/покажи свои сорцы