CODEWITHSUNDEEP
sql-server-2008
sam
sam

Reputation: 1304

where exists not working when used with inner join in sql server 2008 R2

I need to update col2 in #a table with the value in col4 of #b table. If the value in col4 is already present in col2, then col2 should not be updated. In this case since 11 is already present in #a table, it should not be updated for 1 in #a table.

The below code is working fine - 

update a
set a.col2 = b.col4
from #a as a
inner join #b as b
on a.col1 = b.col3 and b.col4 not in (select col2 from #a where col2 is not null)

The problem is when I use WHERE EXISTS instead of NOT IN - 

update a
set a.col2 = b.col4
from #a as a
inner join #b as b
on a.col1 = b.col3
where not exists(select 1 from #b as c where c.col4 = a.col2 and a.col2 is not null)

You can see that this query updates all the values including 11. In this case since 11 is already present in #a table, it should not be updated for 1 in #a table.

Can someone tell what am I doing wrong and why WHERE EXISTS is not working?

Upvotes: 0

Views: 52

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269713

Perhaps if you use #a for the not exists rather than #b it would work as you expect. The equivalent not exists clause is more like this:

update a
set a.col2 = b.col4
from #a a inner join
     #b b
    on a.col1 = b.col3
where not exists (select 1
                  from #a a2 
                  where a2.col2 = b.col4 and a2.col2 is not null
                 );

Upvotes: 1

Related Questions