CODEWITHSUNDEEP
sql-server
user1452705
user1452705

Reputation:

Multiple column and multiple criteria SQL Server Delete?

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.

Using the code from here: MySQL delete multiple rows in one query conditions unique to each row and here Fiddle MySQL

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);

Fiddle SQL Server

How can you achieve the same thing in SQL Server?

Upvotes: 2

Views: 1165

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

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.

Demo here

Upvotes: 3

Related Questions