CODEWITHSUNDEEP
sql-server-2008joinmerge
tough
tough

Reputation: 301

What is the differeance between merge and a join statement?

 1.
 MERGE tbl AS target
USING tb2  AS source 
ON (target.id = source.id)

WHEN MATCHED and source.price >450 
then  
UPDATE SET TARGET.price = SOURCE.price, 
TARGET.group = SOURCE.group 

 2.
 update tb1 
set 
tb1.price=tb2.price, 
tb1.group=tb2.group from tb2 
left join tb1 on tb1.id =tb2.id 
where tb1.id =tb2.id 
and tb1.price>450

I am confused between the above two types of codes. On the second code set, when I do not use tb1.id=tb2.id after where filter, I get lots of nulls when id does not match using with select statement. merge seems fine but do not understand properly how it works.
I want to know are these two sets of codes equivalent? Will the on tb1.id=tb2.id in first behave like a filter as it does in the 2nd set of code? what happens if I omit tb1.id=tb2.id after where from from the second set of codes. I am just confused, with this select and update statements, join statements produces nulls using with select, but what happens while using update? when ever I want to update some tables I would like to see which tables would be effected, sometimes I get confused with this nulls ..... I do not have any formal training, just looking in web and trying to learn but seems too many things to consider. have seen this one I was not clear as well.

The no. of rows on both the tables are not same,tb1 is larger than the tbl2.

Upvotes: 2

Views: 1874

Answers (1)

eidgenossen
eidgenossen

Reputation: 129

Your problem in the second statement is the LEFT join. Short for LEFT OUTER JOIN - i.e. for any rows in tb1 where there is no matching in tb2, the "matching" tb2 values will be null.

the correct form for the update equivalent to the merge would be:

update tb1 set 
    price = source.price, 
    group = source.group 
from tb2 as source
    join tb1 as target 
        on (target.id = source.id)
where source.price > 450;

Upvotes: 1

Related Questions