1

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 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?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
lance
  • 16,092
  • 19
  • 77
  • 136

2 Answers2

1

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
KM.
  • 101,727
  • 34
  • 178
  • 212
  • How might someone see this as preferable to two deletes (first against `BackupDocument`, and then against `Document`, with the OUTPUT clause to `FinishedDocument`) in a transaction? – lance May 20 '10 at 15:40
  • @lance, I'm not sure what you are asking? In any event you will have to have 2 DELETEs, with the INSERT coming from an OUTPUT clause. If you delete one table before the other, that would be a preference based on the activity of your application. I'd delete the least active table first, so if there are any locks they have less impact. – KM. May 20 '10 at 19:28
  • My understanding is that you cannot output into 2 tables either, so how can this work? https://stackoverflow.com/a/37255860 – MikeW Jan 20 '22 at 11:09
0

As KM mentioned, it's 2 deletes.

Or a cascaded foreign key assuming you have a FK. Or a trigger.

gbn
  • 422,506
  • 82
  • 585
  • 676