Delete rows by pair of columns that exist in a table

Question

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?

which database-manager you are using? – MacGucky Mar 25 '11 at 18:42 — MacGucky, Mar 25 '11 at 18:42

score 4 · Accepted Answer · answered Mar 25 '11 at 18:46

4

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

answered Mar 25 '11 at 18:46

JNK

63,321
15
122
138

1

Note that this solution does not work with sqlite. See this SO solution for sqlite: https://stackoverflow.com/questions/24511153/how-delete-table-inner-join-with-other-table-in-sqlite – MOK9 Mar 10 '19 at 17:47

score 1 · Answer 2 · answered Mar 25 '11 at 18:39

1

Try the following sql:

delete from <tablename> 
where (col1 = 12 and col2 = 2) 
   or (col1 = 13 and col2 = 3) 
   or (col1 = 14 and col2 = 2)

answered Mar 25 '11 at 18:39

Howard

38,639
9
64
83

1

Hi, I have lots of pair that must be deleted and I hoped to be able to use something like "IN" statement. no other solution? – Ariyan Mar 25 '11 at 18:41
See e.g. solution provided by JNK. – Howard Mar 25 '11 at 18:48

Delete rows by pair of columns that exist in a table

2 Answers2