Multiple column and multiple criteria SQL Server Delete?

Question

I have tried everything to get this to work. I know it works for MySQL, but I am stumped as to how to get this to work in SQL Server.

DELETE FROM tab 
WHERE (col1, col2) IN (('2016/3/1', 2), ('2016/3/2', 4), ('2016/3/3', 6));

I know you can do this below in SQL Server, but this only addresses one column.

DELETE FROM tab 
WHERE col2 IN (2, 4, 6);

How can you achieve the same thing in SQL Server?

Giorgos Betsos · Accepted Answer · 2016-03-28 07:14:36Z

3

Try this:

DELETE tab
FROM tab 
JOIN (VALUES ('2016/3/1',2),('2016/3/2',4),('2016/3/3',6)) AS t(d, v)
  ON tab.col1 = t.d AND tab.col2 = t.v;

The query uses SQL Server's Table Value Constructor so as to create an in-line table that contains all of the criteria values.

answered Mar 28, 2016 at 7:14

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1