英文:
Output clause in delete statement returns an error when using a table alias
问题
create table Test (a varchar(11));
create table Test2 (a varchar(11));
insert into Test values('a'),('b'),('c');
This one works just fine
delete from Test output deleted.a into Test2(a)
However this one doesn't work
delete t from Test t output deleted.a into Test2(a)
Incorrect syntax near 'OUTPUT'.
I want to be able to use a table alias there since the actual query is something like this:
DELETE t
FROM Test t
LEFT JOIN blah blah
WHERE blah blah
OUTPUT DELETED.a INTO Test2(a)
英文:
create table Test (a varchar(11));
create table Test2 (a varchar(11));
insert into Test values('a'),('b'),('c');
This one works just fine
delete from Test output deleted.a into Test2(a)
However this one doesn't work
delete t from Test t output deleted.a into Test2(a)
> Incorrect syntax near 'OUTPUT'.
I want to be able to use a table alias there since the actual query is something like this:
DELETE t
FROM Test t
LEFT JOIN blah blah
WHERE blah blah
OUTPUT DELETED.a INTO Test2(a)
答案1
得分: 1
你想要的语法是 DELETE <alias> OUTPUT ... FROM ...：
DELETE T
OUTPUT deleted.a INTO dbo.Test2(a)
FROM dbo.Test T;
因此，对于你的 LEFT JOIN 示例，它将是：
DELETE t
OUTPUT DELETED.a
INTO Test2(a)
FROM dbo.Test t
LEFT JOIN dbo.blah b on t.a = b.b --在简单的DELETE上进行LEFT JOIN似乎有点奇怪
WHERE t.c = 2;
英文:
The syntax you want is DELETE <alias> OUTPUT ... FROM ...:
DELETE T
OUTPUT deleted.a INTO dbo.Test2(a)
FROM dbo.Test T;
So, for your LEFT JOIN example, it would be:
DELETE t
OUTPUT DELETED.a
INTO Test2(a)
FROM dbo.Test t
LEFT JOIN dbo.blah b on t.a = b.b --A lEFT JOIN on a simple DELETE seems odd mind
WHERE t.c = 2;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论