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