Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by aditya » UPDATE
Add to briefcase
27 posts, Page 3 of 3
«««123

UPDATE Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
WayneS
Posted Thursday, September 24, 2009 6:50 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 6,367, Visits: 8,227
Bob Hovious 24601 (9/23/2009)

update table1
 set val=a2.val
 from table2 a2
 where table1.id = a2.id


Hey Bob... Jeff has posted about this type of update before. Basically, it's a time bomb waiting to explode, and when it does it will cause extreme slowness in your server.

The first table in the from clause should always be the target table. Therefore, the correct way to write this is:
update table1
 set val=a2.val
 from table1, table2 a2
 where table1.id = a2.id

Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #793631
Simon-413722
Posted Thursday, September 24, 2009 9:00 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:18 AM
Points: 2,110, Visits: 1,284
Its one of the tricky question trying to confuse the reader.

I almost hit NO looking at the example (because it doesnt make sense), but come to think again, the question was asking whether its possible to have FROM in an UPDATE t-sql. Answer is YES. I told myself i would be arguing here if the answer was a NO

Simon Liew
Microsoft Certified Master: SQL Server 2008
Post #793647
VM-723206
Posted Friday, September 25, 2009 7:31 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Saturday, April 06, 2013 12:20 AM
Points: 649, Visits: 263
Very easy!
Post #793804
SuperDBA-207096
Posted Friday, September 25, 2009 8:07 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 02, 2013 12:15 PM
Points: 1,443, Visits: 711
Think there are a few spelling mistakes? An an extra character?

update table
set a1.coulmn=a2.column<
from table1 as a1, table2 as a2
where a1.colum1 = a2.column2
Post #793832
Andrew Watson-478275
Posted Friday, September 25, 2009 8:45 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: Yesterday @ 1:44 AM
Points: 953, Visits: 1,875
VM-723206 (9/25/2009)
Very easy!


To us maybe - but I'm tempted to send a link to the answer to some of our developers, who seem to be incapable of using this syntax.
Post #793868
The Dixie Flatline
Posted Friday, September 25, 2009 1:20 PM


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: Yesterday @ 10:41 AM
Points: 3,788, Visits: 5,538

Jeff has posted about this type of update before. Basically, it's a time bomb waiting to explode, and when it does it will cause extreme slowness in your server.


Thanks for the heads up! In practice, I always use the JOIN format from my first example, so the table to be updated is always the first table. I never use the FROM table1,table2 WHERE format and I think it is being deprecated. But when looking at someone's legacy code I might not have known to check and change if you hadn't warned me. Another SQL-related death avoided.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #794035
BudaCli
Posted Wednesday, September 30, 2009 9:41 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 9:02 AM
Points: 1,046, Visits: 573
Currently using this a lot with SQL 2k5 but on joins.So I guess there's no difference using keyword "JOIN" and joining using a comma

What you don't know won't hurt you but what you know will make you plan to know better
Post #795844
« Prev Topic | Next Topic »

Add to briefcase
27 posts, Page 3 of 3
«««123

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.