架构师之mysql-------------delete where id not in的解决方案

1.前言。
如题。如果直接这样delete会报错

MySql报错： You can't specify target table 'table name' for update in FROM clause

2.解决办法。

CREATE TABLE KK.tmp AS SELECT a.id FROM (SELECT id FROM KK.`table1`) a ;
DELETE FROM KK.`table1` WHERE id NOT IN (SELECT id FROM tmp); 
DROP TABLE KK.tmp;