DELETE from two tables with one OUTPUT clause?
This deletes the document from the Document
table and outputs information about the deleted document into the FinishedDocument
table.
DELETE
FROM Document
OUTPUT Deleted.DocumentId
, Deleted.DocumentDescription
INTO FinishedDocument
WHERE DocumentId = @DocumentId
I need to delete the document not just from the 开发者_Python百科Document
table, but also from the DocumentBackup
table. Meanwhile, I need to maintain insertion into FinishedDocument
.
Is all of this possible with only one statement? If not, is a second DELETE
(against DocumentBackup
), with all of it wrapped in a transaction, the way to go?
You'll need two DELETEs, but you should use two OUTPUTS on the first DELETE to do both the insert into FinishedDocument and into a table variable to save all of the deleted documents.
try:
DECLARE @DeletedDocuments table (DocumentId int)
BEGIN TRANSACTION
DELETE
FROM Document
OUTPUT Deleted.DocumentId, Deleted.DocumentDescription
INTO FinishedDocument
OUTPUT Deleted.DocumentId
INTO @DeletedDocuments
WHERE DocumentId = @DocumentId
DELETE
FROM DocumentBackup
WHERE EXISTS (SELECT 1 FROM @DeletedDocuments d WHERE DocumentBackup.DocumentId=d.DocumentId)
COMMIT
As KM mentioned, it's 2 deletes.
Or a cascaded foreign key assuming you have a FK. Or a trigger.
精彩评论