Help with Delete query 2

[sonname]

I have 2 tables. Lets say TableA and TableB. They are joined by 2 fields, EffortID and SkuID, meaning TableA has an EffortID field that joins with TableB's EffortID and TableA has a field called SkuID and joins with TableB's SkuID. I want to form a query that will delete the records in TableB that are not in TableA.

Ex:
TableA
effortID = 300
SkuID = 200
effortID = 350
SkuID = 250


TableB
effortID = 300
SkuID = 200
effortID = 350
SkuID = 250
EffortID = 600
SkuID = 500

In this situation I would want to delete the record in TableB that has an effortID of 600 and a SkuID of 500

Thanks in advance

 

[vechalapu]

Hi,

delete TableB
where effortID not in (select effortID from TableA) and
SkuID not in (select SkuID from TableA)


Please try this.

 

[jjonesal]

I'm kinda new at SQL Server myself, but I think this will work....

DELETE FROM TableB
FROM TableB INNER JOIN TableA
ON TableB.EffortID = TableA.EffortID
AND TableB.SkuID = TableA.SkuID

Hope this helps...

 

[sonname]

I tried the following, you are on the right track, but the query found the records in TableB that are present in TableA and deleted those. What I want is the opposite, delete the records in TableB that are not present in TableA.

DELETE FROM TableB
FROM TableB INNER JOIN TableA
ON TableB.EffortID = TableA.EffortID
AND TableB.SkuID = TableA.SkuID

 

[jjonesal]

Oops..my bad...I guess I should have read the post a little closer.

Try this:

DELETE FROM TableB b
WHERE NOT EXISTS (SELECT * from TableA a
WHERE a.EffortID = b.EffortID
AND a.SkuID = b.SkuID)