Mysql的sql语句,Delete 中包含 not in
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 .
成功!