SQL: Deleting rows from table based on columns selected from another table

Question

I'm trying to select rows in a table identified by the values in two columns. The values are found in another table. An example should help:

Table1

A | B | C
101 1   x
102 1   o
103 1   o

SELECT A, B FROM Table1 where Table1.C = 'o'

Returns:

A | B
102 1
103 1

I want to delete rows in Table2 where A and B match those returned by the query on Table1.

Table2

A | B | D
101 1   Not deleted
102 1   Deleted
103 1   Deleted

Becomes:

A | B | D
101 1   Not selected

The first select works, but I can't see where to go from there. I currently have the following, but it's throwing an error and I'm not clear what I should be doing. Tutorials welcome as I'm new to SQL.

DELETE Table2
    where A, B in
        (SELECT A, B FROM Table1
    where Table1.C = 'o')

Your first query will only return the 102 row. Can you confirm your input and output data please? — shree.pat18

shree.pat18 shree.pat18 · Accepted Answer · 2015-01-22T11:44:58

You can't specify more than 1 field in the where part when you are using the IN clause as in your query. Instead, you can do this:

delete t from 
table2 t
inner join table1 s on t.a = s.a and t.b = s.b
where s.c = 'o'

Demo

SQL: Deleting rows from table based on columns selected from another table

1 Answers