Post reply

Update JOIN Question

  • May 22, 2008 at 3:17 pm

    #819402

    Hi matija,

    UPDATE p

    SET P.HourlyRate_Calc = ISNULL(w.PPRRate, hr.hourlyrate)

    FROM MQDW.dbo.ETM_PayDataImport p

    LEFT JOIN MQDW.dbo.ETM_PPRWages w

    ON (p.personnum = w.personnum) AND (p.PeriodEndDate = w.periodenddate) AND (p.weekofyear = w.weekofyear)

    This is an example of Updating the Alias. Please check the Alias specified for MQDW.dbo.ETM_PayDataImport. Instead of saying

    Update tab1

    Set tab1.Col2 = tab2.col2

    from tab1

    JOIN tab2

    on tab1.Col1 = tab2.Col2

    You use

    Update a

    set a.Col2 = b.col2

    from tab1 as a

    JOIN tab2 as b

    on a.Col1 = b.Col1

    This is cleaner and it is recommended by quite a bit of people.

    -Roy

  • May 22, 2008 at 3:24 pm

    #819404

    Oh, I see what you mean. Personally, I alias objects if the same object is referenced more than once and/or when referencing table variables.

    And I never (intentionally) claimed that using an alias in the UPDATE statement was not ANSI. 🙂

    ML

    ---
    Matija Lah, SQL Server MVP
    http://milambda.blogspot.com

  • May 22, 2008 at 3:26 pm

    #819405

    Thanks for clearing up..:)

    And you have a cool temper..;)

    -Roy

  • May 22, 2008 at 3:29 pm

    #819407

    That was a mouthful. Thanks so much. Will this allow me to compare each row of data from both tables and determine what the difference is? I was hoping I did not have to select each column\row. Is there a sql command that will look at each row (line by line) and see the difference and then let me know what that difference was?

    Thanks again for your help.

  • May 22, 2008 at 3:30 pm

    #819408

    Please, tell my girlfriend!

    😀

    ML

    ---
    Matija Lah, SQL Server MVP
    http://milambda.blogspot.com

  • April 21, 2015 at 7:04 am

    #1792386

    🙂 So true.

    Roy Ernest (5/22/2008)

    Hi Matija,

    I liked the way you kept your cool. I am not sure why you say that you should not try to Update the Alias? That is ANSI Standard. You claim it is not ANSI. That is the only part I do not agree with you. I was told by another MVP that Updating the Alias is the best way to go. His name is Bill Wunder. He was MVP for 2004,2005 and 2006. So could you please explain?

    Roy

    <hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply