CODEWITHSUNDEEP
sqlsql-servert-sql
i.n.n.m
i.n.n.m

Reputation: 3046

DELETE rows from a table based on a condition in t-sql

I want to delete rows from a table based on a condition from a different table. I want to delete rows in Sales table if all the rows are same in Final_Sales and Sales table. Following is what I am trying,

My t-sql statement,

delete 
    Sales
from 
    Sales
inner join
    Final_Sales on Sales.[time] = Final_Sales.[time] 
        AND Sales.terminal_id = Final_Sales.terminal_id

-- Condition goes here. Check if they have the same records

WHERE time, terminal_id IN (select 
            time, terminal_id count(*)
          from 
            Final_Sales
        group by 
            time, terminal_id
        having 
            count(*) = 1)

This does not work. How can I delete the records in a table based on condition from another table?

Any help would be appreciated.

Upvotes: 0

Views: 1240

Answers (1)

Paul Williams
Paul Williams

Reputation: 17020

SQL Server does not allow multiple expressions on the left side of the IN operator. The IN operator syntax is:

test_expression [ NOT ] IN   
( subquery | expression [ ,...n ]  
)

test_expression is any expression. An expression is "a combination of symbols and operators that the SQL Server Database Engine evaluates to obtain a single data value." (Emphasis mine)

The query could gather up the selected Final_Sales rows first and then delete the Sales rows that match. One way to do this is with a CTE:

;with FinalSales (time, terminal_id, count) as
(
    select time, terminal_id, count(*)
    from Final_Sales
    group by time, terminal_id
    having count(*) = 1
)
delete Sales
from FinalSales
inner join Sales
    on Sales.[time] = FinalSales.[time] 
    and Sales.terminal_id = FinalSales.terminal_id

Upvotes: 1

Related Questions