Sunday, February 19, 2012

How to do bulk Delete

I have a table that has a primary key made of 3 fields (we don't want to use a surrogate key in this situation). In a particular process there is a work table that contains these 3 PK fields and we want to bulk delete them from the base table. Without looping thru the work table, how can I write a Delete statement to delete records in the base table using the work table rows as the criteria?

This syntax illustrates what I want to do, but is not allowed by SQL.

DELETE bt.* FROM basetable bt
INNER JOIN #work wk On bt.fld1 = wk.fld1 And bt.fld2 = wk.fld2 And bt.fld3 = wk.fld3

The correct DELETE statement using TSQL extension is below:

DELETE basetable

FROM basetable bt
INNER JOIN #work wk

ON bt.fld1 = wk.fld1 And bt.fld2 = wk.fld2 And bt.fld3 = wk.fld3

But best is to use the ANSI SQL syntax which doesn't have any ambiguity:

DELETE FROM basetable

WHERE EXISTS(SELECT * FROM #work as wk

WHERE wk.fld1 = basetable.fld1

AND wk.fld2 = basetable.fld2

AND wk.fld3 = basetable.fld3)

|||Ahhh!!! I tried so many variations .... except that one. Thanks.Big Smile

No comments:

Post a Comment