Delete row with null value in columnX only when a copy of the record exists

Question

I have the following table:

and I want to delete records where c3 is null but only when another record with the same values for c1 and c2 already exists. In other words, remove only the row marked with red arrow. How can I do that?

Answer 1

I'll write this as a select, so you don't copy paste and hose something accidentally- you can run it and check that it selects the rows you want to delete

SELECT *
FROM table t1
WHERE 
  t1.c3 IS NULL AND
  EXISTS(
    SELECT null FROM table t2
    WHERE t1.c1 = t2.c1 AND COALESCE(t1.c2, 'value that will never appear in the table') = COALESCE(t2.c2, 'value that will never in the table') AND t2.c3 IS NOT NULL
    )

Conversion to delete should just be a case of replacing the select * with a delete, but Do comment if it doesn't work out..

Edit remarks:

Used coalesce to demo how to make sure that a c2 value of null on row X is equivalent to another c2 value of null on row Y. It is important that the string you choose for 'value that will never appear in the table' is identical in both occurrences within the sql, and that it can never appear in the table, otherwise rows with nulls will match up with other rows that have actual values