Search code examples
sqlsqlitedelete-rowsql-delete

Delete rows by pair of columns that exist in a table

I have a table and I need to delete rows by two columns, for example 

+------+------+--------+
| col1 | col2 |  other |
+------+------+--------+
|  12  |   2  |  test  |
+------+------+--------+
|  14  |   2  |  test1 |
+------+------+--------+
|  12  |   3  |  test2 |
+------+------+--------+
|  13  |   3  |  test3 |
+------+------+--------+
|  15  |   4  |  test4 |
+------+------+--------+

and I want to delete rows that have (col1,col2) pair equal any values in (12,2),(13,3),(14,2)

Can I do this by pure SQL?

Solution

  • If you have a lot of values, populate a table with them and do:

    DELETE t
FROM Table t
INNER JOIN TempTable tt
ON t.col1 = tt.col1
AND t.col2 = tt.col2