Delete Query by joining multiple columns

Question

I have Table T1 with Column C1,C2 and Table T2 with Column C3,C4,C5. I would like delete records from T1 where C1 = C3 AND C2 = C4 and C5 = '123'. What will be the query I tried following

DELETE FROM T1 WHERE (C1,C2) = SELECT (C3,C4) FROM T2 WHERE C5 = '123'

but this is not working.

Answer 1

There is no WHERE (x,y) = (a,b) syntax in SQL Server, sorry. This is how you perform a delete, based on a join, regardless of how many columns are involved in the join:

DELETE t1 
  FROM t1
  INNER JOIN t2
  ON t1.c1 = t2.c3
  AND t1.c2 = t2.c4
  WHERE t2.c5 = '123';

Answer 2

You can do this using exists:

DELETE FROM T1
    WHERE exists (SELECT 1 FROM T2 WHERE C5 = '123' and t2.c3 = t1.c1 and t2.c4 = t1.c2) )

In general, using exists is better than using in with a subquery because NULLs can cause the latter to do behavior in strange ways.

Answer 3

SQL Server supports an extra FROM clause in the DELETE statement.

DELETE FROM T1
FROM T1
INNER JOIN T2
  ON T1.c1 = T2.c3
  AND T1.c2 = T2.c4
  WHERE c5 = '123';