Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Best way to UPDATE with join to other tables... Expand / Collapse
Author
Message
Posted Monday, October 21, 2013 3:04 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, September 8, 2014 3:14 AM
Points: 513, Visits: 1,130
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...
Post #1506589
Posted Monday, October 21, 2013 4:39 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:27 AM
Points: 1,890, Visits: 2,329
First one is better...
I use first option generally



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1506612
Posted Monday, October 21, 2013 7:44 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:21 PM
Points: 3,645, Visits: 7,961
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1506691
Posted Monday, October 21, 2013 7:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:15 PM
Points: 13,731, Visits: 10,692
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1506693
Posted Monday, October 21, 2013 7:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
oops... i havent found any diference except alias used in first query ? , did i overlook something ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1506695
Posted Monday, October 21, 2013 8:37 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 4:52 PM
Points: 590, Visits: 911
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
Post #1506705
Posted Monday, October 21, 2013 9:05 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 21, 2014 9:40 AM
Points: 985, Visits: 1,326
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.


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

http://www.linkedin.com/in/kurtwzimmerman
Post #1506709
Posted Monday, October 21, 2013 9:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 5,369, Visits: 9,898
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
Post #1506711
Posted Monday, October 21, 2013 9:27 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:21 PM
Points: 3,645, Visits: 7,961
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.

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

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1506718
Posted Monday, October 21, 2013 9:35 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 21, 2014 9:40 AM
Points: 985, Visits: 1,326
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.

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
Post #1506726
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse