SQL Question - Please help

Question

Hi All,

i am working on something like, i have two tables

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

Now, i wanna delete those records from Table : 1 whose AwpUnitCost is <= 0, AwpUnitCost IS NULL and it doesnt have the same Ndc from Table : 2.

Thanks

Nick

Answer 1

This may do what your asking. The statement deletes from your first table where AwpUnitCost is less than or equal 0. COALESCE changes null values to 0, so rows with null AwpUnitCost will be included in the delete.

The NOT IN clause includes rows in NdcAwp that are not in InvalidNdcs, based on the Ndc value.

DELETE
FROM NdcAwp
WHERE COALESCE(AwpUnitCost, 0) <= 0
    AND Ndc NOT IN (SELECT Ndc FROM InvalidNdcs)

Answer 2

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
)