SEO

#1 (**permalink**) 02-29-2008, 07:52 PM

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?

#2 (**permalink**) 02-29-2008, 07:52 PM

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

pb648174 wrote:
> 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?

#3 (**permalink**) 02-29-2008, 07:52 PM

On 7 Jul 2006 09:21:23 -0700, pb648174 wrote:

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

Hi pb648174,

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

--
Hugo Kornelis, SQL Server MVP

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Delete statement for a list of items with multiple columns identifying primary key