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 «««123

UPDATE Expand / Collapse
Author
Message
Posted Thursday, September 24, 2009 6:50 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:16 AM
Points: 6,580, Visits: 8,855
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
Posted Thursday, September 24, 2009 9:00 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 6:43 AM
Points: 2,563, Visits: 1,535
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
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: Tuesday, July 23, 2013 6:34 AM
Points: 654, Visits: 265
Very easy!
Post #793804
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 2, 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
Posted Friday, September 25, 2009 8:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 2:42 AM
Points: 1,202, Visits: 2,190
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
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:24 PM
Points: 3,997, Visits: 6,049

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
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: Tuesday, November 5, 2013 6:08 AM
Points: 1,079, Visits: 591
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 «««123

Permissions Expand / Collapse