前记:数据订正需求:删除表A中,不在表B中出现的记录,A表的主键是B表的外键
sql_1:统计数据订正的条数
select count(*)
from table_name_a a
where a.column1 <> 'haha' and a.gmt_create>='2013-12-02 00:00:00'
and not exists (select * from table_name_b b where a.id=b.a_id);
sql_2:于是想当然将sql_1中的select更改为delete
delete from
table_name_a a
where a.column1 <> 'haha' and a.gmt_create>='2013-12-02 00:00:00'
and not exists (select * from table_name_b b where a.id=b.a_id);
执行sql_2返回语法错误,百度找到 http://xuliangyong.iteye.com/blog/427998 解决方案--给表起个别名,更改为sql_3:
delete from table_name_a where id in
(select id from
(select id
from table_name_a a
where a.column1 <> 'haha' and a.gmt_create>='2013-12-02 00:00:00'
and not exists (select * from table_name_b b where a.id=b.a_id)
) t);
sql_2失败的原因,在mysql官网 http://dev.mysql.com/doc/refman/5.6/en/subqueries.html 对子查询说明里有这样一句:
“ In MySQL, you cannot modify a table and select from the same table in a subquery. This applies to statements such as DELETE, INSERT, REPLACE, UPDATE”
delete,insert语句时,如果有子查询,子查询里的表和delete,insert操作的表不能是同一个。
insert into table_a(id,name) values(11,'Lucy');
insert into table_a(id,name) values(12,'YangHong');
insert into table_a(id,name) values(13,'XiaoMing');
insert into table_a(id,name) values(14,'Jack');
insert into table_b(id,a_id,score) values(1,11,85);
insert into table_b(id,a_id,score) values(2,14,98);
delete from table_a
where not exists (select * from table_b where table_b.a_id=table_a.id);
不知道为什么?莫非是本机mysql版本更新?