2
 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.

Community
  • 1
  • 1
tough
  • 301
  • 1
  • 7
  • 14
  • 1
    I don't know that I would ever use a `MERGE` for a single conditional join. The beauty of `MERGE` is that it can take multiple scenarios (insert, update, delete - adding many variations based on clauses) and solve them all in a single statement. You're not doing that here. Also I suggest you properly alias your columns on lines 3 and 4 of your update. Which price? Which group? Be explicit. – Aaron Bertrand Nov 26 '12 at 18:19
  • @AaronBertrand Thanks for your response, I am trying to do the same (doing `insert, update, and delete` upon requirement) using `merge` was difficult for me to know how it behaves since I cannot use it as I can do it on `join` to see what results I am going to change. so willing to understand the part specifically is "Will the `on tb1.id=tb2.id` in first behave like a filter as it does in the 2nd set of code after `where` condition?" Using `join` and `on` I have to use again another condition to filter out the ones I get as nulls right? So, does the `on` on `merge`does the same?didn't get it – tough Nov 27 '12 at 01:39
  • @AaronBertrand I thought `update tb1` would make it explicit, won't it? edited now though. I apologize for low level question. – tough Nov 27 '12 at 01:46
  • 1
    If you're not fluent with `MERGE` I would suggest continuing to write code that you can verify. The `MERGE` syntax is quite daunting, and contrary to popular belief, it [does not completely isolate you from race conditions](http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx) that can plague separate statements. So other than brevity `MERGE` doesn't buy you too much over *well-planned* individual statements. – Aaron Bertrand Nov 27 '12 at 01:48
  • @AaronBertrand Thanks for the link, That really helps to deepen it. – tough Nov 27 '12 at 01:57

1 Answers1

1

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;
eidgenossen
  • 129
  • 5