CODEWITHSUNDEEP
sql
TagJones
TagJones

Reputation: 41

delete from where exists (select...) deleting all rows

I want to delete the rows of a table that are returned from a select statement.

Here is an simplified example of it not working.

declare @t1 table(a int, b int, c int)

insert into @t1(a,b,c) select 1, 10, 1
insert into @t1(a,b,c) select 1, 5, 2

select ta.a, ta.b, ta.c
        from @t1 ta
        join @t1 tb on ta.a = tb.a
        where ta.c < tb.c

delete from @t1
where exists (select ta.a, ta.b, ta.c
        from @t1 ta
        join @t1 tb on ta.a = tb.a
        where ta.c < tb.c)

select * from @t1

Here are the outputs, you can see the select only selects a single row but the delete deletes everything

results

Upvotes: 4

Views: 3241

Answers (1)

Siyual
Siyual

Reputation: 16917

The problem is the fact that you're using EXISTS.

EXISTS only evaluates whether or not there is a result at all, and since your statement is returning records, you're essentially saying: DELETE @T1 WHERE (TRUE)

Try using this instead:

Delete  ta
From    @t1 ta
Join    @t1 tb  On  ta.a = tb.a
Where   ta.c < tb.c

Upvotes: 8

Related Questions