使用左连接和表变量删除

Maybe I'm missing a bracket or something but I'm having a hard time deleting rows from a table variable where I'm left joining and looking for the key I'm joining on. If it has a value, then I get rid of it. The problem is that I can't get the query to parse. Any ideas?

也许我错过了一个括号或其他东西,但是我很难从表变量中删除行,我正在加入并寻找我正在加入的密钥。如果它有一个值,那么我就摆脱它了。问题是我无法解析查询。有任何想法吗?

declare @MrTemp ( key1 int ,key2 int )

声明@MrTemp(key1 int,key2 int)

insert into @MrTemp select key1, key2 from ASourceTable

插入@MrTemp从ASourceTable中选择key1,key2

delete
from @MrTemp mt left join ARealTable art on mt.key1 = art.key1 and mt.key2 = art.key2 where art.key1 is not null and art.key2 is not null

从@MrTemp删除mt left在mt.key1 = art.key1和mt.key2 = art.key2上加入ARealTable art,其中art.key1不为null且art.key2不为null

3 个解决方案

#1

DELETE @MrTemp
FROM @MrTemp mt LEFT JOIN ...

#2

You can only delete from one table at a time:

您一次只能从一个表中删除:

to delete from @MrTemp [where there is a matching record in ARealTable]

从@MrTemp中删除[其中ARealTable中有匹配的记录]

delete mt
from @MrTemp mt left join ARealTable art on mt.key1 = art.key1 and mt.key2 = art.key2 where art.key1 is not null and art.key2 is not nu

or. to delete from ARealTable [again where the record has a corresponding record in ARealTable]

要么。从ARealTable中删除[再次记录在ARealTable中有相应的记录]

delete art
from @MrTemp mt left join ARealTable art on mt.key1 = art.key1 and mt.key2 = art.key2 where art.key1 is not null and art.key2 is not null

#3

You need to reference the alias after the delete but before the from for the table you want to delete from

您需要在删除之后但在from之前引用要删除的表的别名

delete art 
from @MrTemp mt left join ARealTable art on 
mt.key1 = art.key1 and mt.key2 = art.key2 
where art.key1 is not null and art.key2 is not null

#1

DELETE @MrTemp
FROM @MrTemp mt LEFT JOIN ...

#2