SQL Server : DELETE rows having more than 1 record (NULL)

Question

I have a table with part numbers and associated component part no and location.

Each part_no may have multiple components or not at all.

Here's an example

Part_No | Component_Part_No | Location
-----------------------------------------    
 12345  | NULL              | 001
 50001  | NULL              | 001
 50001  | 4001              | 001
 50001  | 4002              | 001 

The part no 5001 has a NULL value as well as other components. I want to be able to DELETE the row that has a NULL value for part 50001 but keep part no 12345 intact. The issue is that the NULL value for 50001 is not correct in the table and I need to remove those rows.

Any help is appreciated.

Thank you

Answer 1

DELETE from table
WHERE component_part_no IS NULL
AND part_no in
    (SELECT part_no 
    FROM table
    WHERE component_part_no IS NOT NULL)

Answer 2

delete from table where Part_No = 50001 and Component_Part_No IS NULL