Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sql server DELETE and WITH clause

Tags:

sql-server

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?

like image 972
ala Avatar asked Jul 24 '09 13:07

ala


People also ask

How do you delete multiple conditions in SQL?

Example - Using Two conditions For example: DELETE FROM employees WHERE last_name = 'Johnson' AND employee_id >= 80; This SQL Server DELETE example would delete all records from the employees table where the last_name is 'Johnson' and the employee_id is greater than or equal to 80.

Can we use delete with CTE?

Common Table Expressions (CTE) A Common Table Expression (CTE) is a temporary result set that can be referenced within another SELECT, INSERT, UPDATE, or DELETE statement.

How do I delete a row based on a condition in SQL Server?

To remove one or more rows in a table: First, you specify the table name where you want to remove data in the DELETE FROM clause. Second, you put a condition in the WHERE clause to specify which rows to remove. If you omit the WHERE clause, the statement will remove all rows in the table.

Does delete need a WHERE clause?

If you run a DELETE statement with no conditions in the WHERE clause, all of the records from the table will be deleted. As a result, you will most often include a WHERE clause with at least one condition in your DELETE statement.


2 Answers

This should do it:

DELETE Table1
 from Table1 t1
  inner join tempTable t2
   on t2.Col1 = t1.Col1
    and t2.Col2 = t1.Col2
like image 79
Philip Kelley Avatar answered Sep 30 '22 08:09

Philip Kelley


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
like image 30
Joel Coehoorn Avatar answered Sep 30 '22 08:09

Joel Coehoorn