Delete items that are found in a second table 2

[hapax]

I have a table named NoteTable. I also have a second temporary table that contains some of the identical data as NoteTable.

I want to delete all rows from NoteTable where the same items exist in the temp table, and also delete those same items from the temp table.

Example:


NoteTable contents:
NoteID NoteSeqNum NoteTypeID
12654 232 1
12654 232 3
22520 126 1
22661 282 1
22661 282 5

@NoteTempTable contents:
NoteID NoteSeqNum NoteTypeID
12654 232 1
22520 126 1
22661 282 1

NoteTable contents after delete:
NoteID NoteSeqNum NoteTypeID
12654 232 3
22661 282 5

@NoteTempTable contents after delete:
...[empty]


Partial SQL for what I'm trying to do:

DECLARE @NoteTempTable TABLE(NoteId INT, NoteSeqNum INT);

[...fill @NoteTempTable]


DELETE FROM NoteTable
WHERE
[...a matching NoteId and NoteTypeID are found in @NoteTempTable]

DELETE FROM @NoteTempTable
WHERE
[...the same rows you removed in the above DELETE]

 

[hapax]

Sorry, I forgot to include NoteSeqNum in the criteria for delete.

So the partial SQL should say:

DELETE FROM NoteTable
WHERE
[...a matching NoteId, NoteSeqNum, and NoteTypeID are found in @NoteTempTable]

DELETE FROM @NoteTempTable
WHERE
[...the same rows you removed in the above DELETE]

 

[gmmastros]

try this:

Delete	NoteTable
From	NoteTable
        Inner Join @NoteTempTable NTT
          On  NoteTable.NoteId = NTT.NoteId
          And NoteTable.NoteSeqNum = NTT.NoteSeqNum
          And NoteTable.NoteTypeId = NTT.NoteTypeId

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian101]

That is not going to also delete them from the @NoteTempTable table is it George?

Simi

 

[gmmastros]

no.

With T-SQL, you can only delete from one table at a time.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Don't know if there is any other database language allowing to delete from multiple tables by one command (same for insert and update).