T-SQL与删除语句一起使用交叉应用 [英] T-SQL Using Cross-Apply with Delete Statement

问题描述

我有下表:

RecordID
101
102
103
104
105
106

TableOne
101
102
103
104

TableTwo


TableThree
101
102

，我需要删除其他表中未包含的RecordsID行.请注意，有时表TableOne，TableTwo，TableThree中的一个可能为空，因此不应删除任何记录.

and I need to delete the RecordsID rows, that are not included in the other tables. Please, note that sometimes the one of the tables TableOne,TableTwo,TableThree could be empty and no records should be deleted then.

结果表应为:

RecordID
101
102

由于有空表，因此我无法使用INNER JOIN.而且由于我在函数中使用这些代码，因此无法创建仅包含带有记录的表的动态SQL语句并执行它.

Because of the empty tables I am not able to use INNER JOIN. And because I am using these code in a function I am not able to make a dynamic SQL statement containing only tables with records and executed it.

我可以使用IF语句来做到这一点，但是在我的实际情况下，我有很多情况要检查，要连接的表很多，结果会有很多代码重复.

I could this with IF statements, but in my real situation I have many cases to check and many tables to join and a lot of code duplication is going as a result.

这就是为什么我开始怀疑，有没有办法使用CROSS APPLY来做到这一点呢?

That's why I started to wonder is there a way to do this cleverer and cleaner with CROSS APPLY?

推荐答案

我在这里看不到使用cross的任何优势.这是完成任务的简单解决方案:

I don't see any advanage in using cross apply here. Here is a simple solution that does the job:

declare @t table(recordid int)
declare @tableone table(recordid int)
declare @tabletwo table(recordid int)
declare @tablethree table(recordid int)
insert @t values(101),(102),(103),(104),(105),(106)

insert @tableone values(101),(102),(103),(104)
insert @tablethree values(101),(102)

delete t
from @t t
where not exists (select 1 from @tableone where t.recordid = recordid)
and exists (select 1 from @tableone)
or not exists (select 1 from @tabletwo where t.recordid = recordid)
and exists (select 1 from @tabletwo)
or not exists (select 1 from @tablethree where t.recordid = recordid)
and exists (select 1 from @tablethree)

结果:

recordid
101
102

这篇关于T-SQL与删除语句一起使用交叉应用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！