SQL Server DELETE和WITH子句

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

我需要构建一个SQL语句来从某个表中删除与另一个select语句匹配的记录。

In Teradata we use

在Teradata我们使用

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:

在SQL Server中，不允许在WHERE..IN子句中包含多于一列的列。我以为我可以使用WITH子句：

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?

如何使用WITH..DELETE子句？还有另一种方式吗？

5 个解决方案

#1

This should do it:

这应该这样做：

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

#2

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:

测试它以确保它准确返回您要删除的行。然后通过将“SELECT”更改为“DELETE”并删除列列表将其转换为删除语句：

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

#3

delete from table1 t1 where exists 
  ( 

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

)

#4

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

#5

This works for me

这对我有用

WITH CTE AS
(
SELECT TOP 50000 *
from v020101hist  order by data
)
DELETE FROM CTE

#1