CombatCaptain CombatCaptain - 4 months ago 11
SQL Question

How to delete records from a table based on conditions from a joined set?

I have two tables

Table : 1, Name : NdcAwp, Columns-Ndc, AwpUnitCost
Table : 2, Name : InvalidNdcs, Column-Ndc


Now, I want to delete those records from Table : 1 whose AwpUnitCost is <= 0, AwpUnitCost IS NULL and it doesn't have the same Ndc from Table : 2.

Answer

I believe this is what you want:

DELETE FROM NdcAwp
WHERE (AwpUnitCost <= 0
OR AwpUnitCost IS NULL)
AND Ndc NOT IN
(
SELECT Ndc
FROM InvalidNdcs
)