Post reply

Best way to UPDATE with join to other tables...

  • October 21, 2013 at 3:04 am

    #300296

    Hi,

    What's the best way, so locks can be avoided and other problems, a table with joins to other ones?

    I read that using alias on the table being updated can cause locks and I see o lot of those...

    UPDATE t SET t.c0 = j.c0, t.c1 = j.c1 FROM tableToUpdate t INNER JOIN joinTable j ON t.Id = p.IdFromT

    -- vs --

    UPDATE tableToUpdate SET tableToUpdate.c0 = j.c0, tableToUpdate.c1 = j.c1 FROM joinTable j WHERE tableToUpdate .Id = p.IdFromT

    Thanks,

    Pedro


    If you need to work better, try working less...

  • October 21, 2013 at 4:39 am

    #1660076

    First one is better...

    I use first option generally

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • October 21, 2013 at 7:44 am

    #1660146

    Locks will happen on both updates. The difference might be the JOIN type which uses different standards. The first query uses ANSI-92 standard and the second one uses ANSI-89 standard. AFAIK, both queries will be interpreted the same way.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • October 21, 2013 at 7:49 am

    #1660148

    Personally I use the first options as it is much clearer on what you want to achieve.

    I think Luis is correct in asserting that both queries are interpreted the same way. You could check the query plan to make sure.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • October 21, 2013 at 7:52 am

    #1660150

    oops... i havent found any diference except alias used in first query ? :w00t: , did i overlook something ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • October 21, 2013 at 8:37 am

    #1660158

    If you create those tables and add some test data and run both queries you will notice that you get the same exact plan. The two statements are identical.

    As far as the locks go they will obtain the same locks you can't avoid locks being take on an update operation. It will have to request update locks are all rows that will be updated and then those locks will move to exclusive locks once they are all ready.

    So it come down to readability and for that I prefer the first query using the "join" statement.


    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • October 21, 2013 at 9:05 am

    #1660161

    Luis Cazares (10/21/2013)

    Locks will happen on both updates. The difference might be the JOIN type which uses different standards. The first query uses ANSI-92 standard and the second one uses ANSI-89 standard. AFAIK, both queries will be interpreted the same way.

    OK, so looking at both queries I'd have to say I'm "old school" cuz I would have written it the second way. :Whistling:

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • October 21, 2013 at 9:12 am

    #1660163

    Beware, though - if you have multiple matches in joinTable for any rows in tableToUpdate then you'll get unexpected results. This is known as a cardinality error, and the frightening thing is that you won't know it's happened, because there'll be no error or warning message.

    John

  • October 21, 2013 at 9:27 am

    #1660168

    Kurt W. Zimmerman (10/21/2013)

    Luis Cazares (10/21/2013)

    Locks will happen on both updates. The difference might be the JOIN type which uses different standards. The first query uses ANSI-92 standard and the second one uses ANSI-89 standard. AFAIK, both queries will be interpreted the same way.

    OK, so looking at both queries I'd have to say I'm "old school" cuz I would have written it the second way. :Whistling:

    I would suggest you to change your habits as the outer joins in the ANSI-89 standard are no longer supported on SQL Server. Who knows, maybe they'll force complete ANSI-92 standard in future versions.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • October 21, 2013 at 9:35 am

    #1660174

    Luis Cazares (10/21/2013)

    Kurt W. Zimmerman (10/21/2013)

    Luis Cazares (10/21/2013)

    Locks will happen on both updates. The difference might be the JOIN type which uses different standards. The first query uses ANSI-92 standard and the second one uses ANSI-89 standard. AFAIK, both queries will be interpreted the same way.

    OK, so looking at both queries I'd have to say I'm "old school" cuz I would have written it the second way. :Whistling:

    I would suggest you to change your habits as the outer joins in the ANSI-89 standard are no longer supported on SQL Server. Who knows, maybe they'll force complete ANSI-92 standard in future versions.

    Some habits are easy to break... This is one of them.

    Thanks Luis.

    Kurt

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • October 21, 2013 at 6:32 pm

    #1660349

    John Mitchell-245523 (10/21/2013)

    Beware, though - if you have multiple matches in joinTable for any rows in tableToUpdate then you'll get unexpected results. This is known as a cardinality error, and the frightening thing is that you won't know it's happened, because there'll be no error or warning message.

    John

    Example of this here: A Hazard of Using the SQL Update Statement [/url]

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 11 posts - 1 through 10 (of 10 total)

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