Sql server DELETE and WITH clause

Question

I need to build an SQL statement to delete from certain table the records that match another select statement.

In Teradata we use

delete from table1 
where (col1, col2) in (
  select col1,col2
  from table2
)

While in SQL Server it's not allowed to have more than 1 column in the WHERE..IN clause. I thought I can use the WITH clause:

with tempTable(col1,col2) as (
select col1,col2
from table2
)
delete from table1
where table1.col1 = tempTable.col1
and table1.col2 = tempTable.col2

How to use WITH..DELETE clause? Is there another way?

Answer 1

This should do it:

DELETE Table1
 from Table1 t1
  inner join tempTable t2
   on t2.Col1 = t1.Col1
    and t2.Col2 = t1.Col2

Answer 2

delete from table1 t1 where exists 
  ( 

   select 1 from table2 t2 where t1.col1 = t2.col1 and t1.col2 > t2.col2

)

Answer 3

First build a query that selects the rows you need:

SELECT t1.*
FROM [Table1] t1
INNER JOIN [Table2] t2 ON t1.[col1] = t2.[col1] AND t1.[Col2]=t2.[Col2]

Test it to make sure it returns exactly the rows you want to delete. Then turn it into a delete statement by changing the "SELECT" to "DELETE" and removing the column list:

DELETE t1
FROM [Table1] t1
INNER JOIN [Table2] t2 ON t1.[col1] = t2.[col1] AND t1.[Col