mysql delete not in

想要执行：

DELETE FROM tb_tableA WHERE id IN (
        SELECT a.id FROM tb_tableA a WHERE a.id NOT IN ( 
                SELECT a_id FROM tb_tableB 
        ) 
);

是无法正确执行的。

解决方案：创建临时表，作为中间表；用完再删去。

 

CREATE TABLE tmp AS SELECT t.id FROM (
        SELECT a.id FROM tb_tableA a WHERE a.id NOT IN ( 
                SELECT a_id FROM tb_tableB 
        ) 
) t;
DELETE FROM tb_tableA WHERE id IN (SELECT id FROM tmp); 
DROP TABLE tmp;

【说明】：tb_tableB 中 字段 a_id 是 tb_tableA 的主键 id .

 

 

成功！