DELETE from a SELECT statement

Transact-SQL
1

I need to delete all rows that are being returned from this set:

SELECT IC1.LocationID AS DefunctLoc, IC1.DOB, IC1.ItemID
FROM dbo.ItemCost IC1
JOIN dbo.ItemCost IC2 ON IC1.ItemID=IC2.ItemID
AND IC1.DOB=IC2.DOB
AND IC1.ItemID=IC2.ItemID
WHERE IC1.LocationID IN (8)

How can I accomplish this?

To illustrate my problem even more, the issue came to my plate because following UPDATE is failing due duplicate entries:

UPDATE ItemCost SET LocationID=5 WHERE LocationID=8

8 is a location that should be deleted (was actually renamed it to 5) but someone insert records while I was finishing the process and affected this table.

A result set example of my problem would be:

LocationID DOB ItemID
5 2016-09-01 00:00:00 1238
8 2016-09-01 00:00:00 1238

There are 4 more columns but above are the composited PK.

2

Is it possible to do something like

BEGIN TRAN
DELETE IC1 
--SELECT IC1.LocationID AS DefunctLoc, IC1.DOB, IC1.ItemID
FROM dbo.ItemCost IC1
JOIN dbo.ItemCost IC2 ON IC1.ItemID=IC2.ItemID
	AND IC1.DOB=IC2.DOB 
	AND IC1.ItemID=IC2.ItemID
WHERE IC1.LocationID IN (8)

ROLLBACK TRAN;
--COMMIT TRAN;
3

You need to be sure to UPDATE an alias name anytime you use JOIN in an UPDATE (or DELETE):

UPDATE IC1
SET LocationID = 5
FROM dbo.ItemCost IC1
JOIN dbo.ItemCost IC2 ON IC1.ItemID=IC2.ItemID
AND IC1.DOB=IC2.DOB
AND IC1.ItemID=IC2.ItemID
WHERE IC1.LocationID IN (8)

4

Thanks!

5

if you want to delete results of a select query you can use CTEs as well

with dltCTE
as
(
SELECT IC1.LocationID AS DefunctLoc, IC1.DOB, IC1.ItemID
FROM dbo.ItemCost IC1
JOIN dbo.ItemCost IC2 ON IC1.ItemID=IC2.ItemID
AND IC1.DOB=IC2.DOB 
AND IC1.ItemID=IC2.ItemID
WHERE IC1.LocationID IN (8)
)
Delete dltCTE
6

I don't know what other folk think about this method?, but I try to avoid it as it requires code-changes to test the Delete (in a way that I think is more error-prone - but it might just be that I am a dinosaur! and stuck in my ways!)

DELETE IC1 
--SELECT IC1.LocationID AS DefunctLoc, IC1.DOB, IC1.ItemID
FROM dbo.ItemCost IC1
JOIN dbo.ItemCost IC2 ON IC1.ItemID=IC2.ItemID
	AND IC1.DOB=IC2.DOB 
	AND IC1.ItemID=IC2.ItemID
WHERE IC1.LocationID IN (8)

has the Comment Toggle for a SELECT (to test) or a DELETE at the top of the statement

with dltCTE
as
(
SELECT IC1.LocationID AS DefunctLoc, IC1.DOB, IC1.ItemID
FROM dbo.ItemCost IC1
JOIN dbo.ItemCost IC2 ON IC1.ItemID=IC2.ItemID
AND IC1.DOB=IC2.DOB 
AND IC1.ItemID=IC2.ItemID
WHERE IC1.LocationID IN (8)
)
--SELECT dltCTE.LocationID AS DefunctLoc, dltCTE.DOB, dltCTE.ItemID
Delete dltCTE

has the comment at the bottom, which is not a lot different, but in practice I find that CTEs are more usually used in combination with additional code:

with dltCTE
as
(
SELECT IC1.LocationID AS DefunctLoc, IC1.DOB, IC1.ItemID
FROM dbo.ItemCost IC1
JOIN dbo.ItemCost IC2 ON IC1.ItemID=IC2.ItemID
AND IC1.DOB=IC2.DOB 
AND IC1.ItemID=IC2.ItemID
WHERE IC1.LocationID IN (8)
)
--SELECT dltCTE.LocationID AS DefunctLoc, dltCTE.DOB, dltCTE.ItemID
Delete D
FROM dltCTE AS D
    JOIN SomeOtherTable
       ON SomeColumn = SomeOtherColumn

and now the debug code is in the middle of the statement which IME increases the likelihood of accidents.

7

Nice ideas. I deleted the rows several days ago, but learned couple of new ways. Thanks.