Richard Jarrell Richard Jarrell - 18 days ago 5
SQL Question

SQL Deleting All Records If ColA Matches and ColB Matches Sometimes

I need to delete all records from a SQL table where ColumnA matches an ID that sometimes has ColumnB equal 0. In other words, if any data for an ID is bad, get rid of all data.

ColumnA ColumnB
------- -------
11 0
11 1
11 0
12 1
12 1


In this data, I would want to delete anything where ColumnA is 11 because sometimes ColumnB is 0.

Answer

The ANSI standard syntax is:

delete from t
    where exists (select 1
                  from t t2
                  where t2.ColumnA = t.ColumnA and t2.ColumnB = 0
                 );

You can also use in:

delete from t
    where t.ColumnA in (select t2.ColumnA
                        from t t2
                        where t2.ColumnB = 0
                       );