mysql不支持删除嵌套查询。
比如如下语句:
想删除一些脏数据
DELETE FROM merchandise WHERE merchandise_id NOT IN ( SELECT parent_id FROM merchandise WHERE is_union=0)
但是运行报错。
这时可以使用临时表:就是将数据先存入临时表,用完后在删除临时表
CREATE TEMPORARY TABLE mytempSELECT WHERE is_union=0;
DELETE FROM merchandise WHERE merchandise_id NOT IN (select parent_id FROM mytemp);
drop table mytemp;
分成三条数据即可。
另外
select * FROM merchandise WHERE merchandise_id NOT IN ( SELECT parent_id FROM merchandise WHERE is_union=0)
如果嵌套的select查询有null值,则此语句不起作用,
DELETE FROM merchandise WHERE merchandise_id NOT IN ( SELECT parent_id FROM merchandise WHERE is_union=0 and parent_id is not null)