delete from... not exisits

最新推荐文章于 2023-06-25 17:44:14 发布

原创最新推荐文章于 2023-06-25 17:44:14 发布 · 9.3k 阅读

1 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

10 篇文章

订阅专栏

本文详细阐述了在MySQL中使用SQL语句删除与子查询结果不匹配的记录时遇到的语法错误及解决方法。通过实例演示了如何正确地使用别名来避免错误，并解释了MySQL中不允许在删除、插入语句中引用同一表的子查询的原因。

前记：数据订正需求：删除表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操作的表不能是同一个。

不过当我在本机mysql测试环境实验时，却成功了。

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);