Delete statement for a list of items with multiple columns identifying primary key -- sql server (alternate)

pb648174
7/7/2006 9:21:23 AM

I frequently have the problem where I have a list of items to delete in
a temp table, such as

ProjectId Description
------------- ----------------
1 test1
2 test4
3 test3
4 test2

And I want to delete all those items from another table.. What is the
best way to do that? If I use two IN clauses it will do it where it
matches anything in both, not the exact combination of the two. I can't
do joins in a delete clause like an update, so how is this typically
handled?

The only way I can see so far to get around it is to concatenate the
columns like CAST(ProjectId as varchar) + '-' + Description and do an
IN clause on that which is pretty nasty.

Any better way?

Query Builder
7/7/2006 9:55:50 AM

To be accurate, you CAN do joins in a delete clause...

Delete A
FROM TblA AS A
Inner JOin TblB AS B
Where A.Key1 = B.Key1

This will remove all recoreds in Table A that matches the keys in Table
B. You may add a where clause to filter records....

It would help if you can give more schema info of those tables that you
are trying to delete....

[quoted text, click to view]

Hugo Kornelis
7/7/2006 11:16:26 PM

[quoted text, click to view]

Hi pb648174,

DELETE FROM AnotherTable
WHERE EXISTS
(SELECT *
FROM #ItemsToDelete AS d
WHERE AnotherTable.ProjectId = d.ProjectID
AND AnotherTable.Description = d.Description)

--

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server (alternate)

sql server (alternate) : Delete statement for a list of items with multiple columns identifying primary key