SQL Delete 清除表而不是出错 [英] SQL Delete clears the table instead of erroring

问题描述

我有一段(您会认为)不会编译的 SQL，而是从目标表中删除所有行.

考虑这个设置:

创建表TableA (ColumnA varchar(200));创建表 TableB (ColumnB varchar(200));插入 TableA 值 ('A'),('B'),('C');插入 TableB 值('A')；

然后是下面的sql:

--返回TableA中的所有行从表A中选择*；-- 不会出错(ColumnA 在 TableB 上不存在)删除 TableA where ColumnA in(从 TableB 中选择 ColumnA)--没有返回行从表A中选择*；

上面的delete语句导致从TableA中删除所有行，而不是报错ColumnA在TableB中不存在>

这里有一个 SQL Fiddle 演示:http://www.sqlfiddle.com/#!3/9d883/6

看来 TableA 中的 ColumnA 正在被拾取，但预计它会超出范围".

这是为什么?

解决方案

由于内部查询中的 ColumnA 与外部查询之间的相关性，这按预期工作.

这个常用的相关查询模式是有效的

DELETE TableA WHERE NOT EXISTS (select * from TableB where TableB.ID=TableA.ID)

它删除在 TableB 中没有相关记录的 TableA 条目.

它表明您可以在相关查询中引用 TableA 列.在您的查询中

delete TableA where ColumnA in (select ColumnA from TableB)

内部查询正在生成

• TableB 中的每条记录占一行
• 每行一列，其值为来自外部查询的 ColumnA

所以 DELETE 会通过

I have a piece of SQL which (you would think) wouldn't compile, but which instead deletes all rows from the target table.

Consider this setup:

create table TableA (ColumnA varchar(200));
create table TableB (ColumnB varchar(200));

insert TableA values ('A'),('B'),('C');
insert TableB values ('A');

Then the following sql:

--Returns all rows from TableA
select * from TableA;

--Does not error (ColumnA does not exist on TableB)
delete TableA where ColumnA in (select ColumnA from TableB)

--No Rows are returned
select * from TableA;

The delete statement above causes all rows to be removed from TableA, rather than erroring that ColumnA doesn't exist in TableB

There's a SQL Fiddle demontrating this here: http://www.sqlfiddle.com/#!3/9d883/6

It seems that the ColumnA from TableA is being picked up, but expected it to be "out of scope".

Why is this?

解决方案

That works as expected, due to the correlation between ColumnA in the inner query to the outer.

This commonly used correlated query pattern is valid

DELETE TableA WHERE NOT EXISTS (select * from TableB where TableB.ID=TableA.ID)

It removes TableA entries that don't have a dependent record in TableB.

It shows that you can reference TableA columns in a correlated query. In your query

delete TableA where ColumnA in (select ColumnA from TableB)

The inner query is producing

• one row for each record in TableB
• one column for each row, whose value is ColumnA from outer query

So the DELETE goes through

这篇关于SQL Delete 清除表而不是出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！