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 Gregory Larsen » stairway to T-SQL DML Level 10: Changing Data...
Add to briefcase

stairway to T-SQL DML Level 10: Changing Data with the UPDATE Statement Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Greg Larsen
Posted Wednesday, February 22, 2012 5:25 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, January 19, 2013 8:28 AM
Points: 1,038, Visits: 255
Comments posted to this topic are about the item stairway to T-SQL DML Level 10: Changing Data with the UPDATE Statement

Gregory A. Larsen, MVP

Need SQL Server Examples check out my website at http://www.sqlserverexamples.com
Post #1255858
Mr.DiGi
Posted Wednesday, October 10, 2012 12:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 06, 2013 9:46 AM
Points: 2, Visits: 53
I like "less magic" - you can update table alias:

UPDATE
  P
SET
  ProductName = N.ProductName, 
  Price = N.Price
FROM
  dbo.Product P
INNER JOIN
  dbo.New_Prices N ON P.ID = N.ID;


This is great when you join "Product" table more than once.
Post #1370708
simon.duckett
Posted Wednesday, October 10, 2012 12:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 21, 2012 2:09 AM
Points: 4, Visits: 56
Good article.

Listing 7 is an incorrect script. It is a repeat of Script 6.
Post #1370709
RichB
Posted Wednesday, October 10, 2012 4:30 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:06 AM
Points: 1,026, Visits: 751
How about joined aggregate values?

Also, you missed out the rather handy ability to use variables in the set.

eg
set @Variable = column = @variable + stuff

etc


Post #1370824
INCREDIBLEmouse
Posted Wednesday, October 10, 2012 5:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 7:06 AM
Points: 7, Visits: 52
simon.duckett (10/10/2012)
Good article.

Listing 7 is an incorrect script. It is a repeat of Script 6.


Correct, I second that. The first script example under the .WRITE section is a duplicate of the preceding script example.
Post #1370860
gorthog
Posted Wednesday, October 10, 2012 7:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 07, 2013 11:07 AM
Points: 266, Visits: 159
Early in the article you mention the benefit of creating a SELECT statement to preview the rows that will be updated. Here is a useful form for previewing the changes:

UPDATE p SET
--SELECT p.*, 
ProductName = 'WingDing'
, Price = 19.27
FROM Product p
WHERE p.ProductName = 'WingDng'
Post #1370929
John Mitchell-245523
Posted Thursday, October 11, 2012 3:50 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:38 AM
Points: 4,422, Visits: 7,179
Mr.DiGi (10/10/2012)
I like "less magic" - you can update table alias:

UPDATE
  P
SET
  ProductName = N.ProductName, 
  Price = N.Price
FROM
  dbo.Product P
INNER JOIN
  dbo.New_Prices N ON P.ID = N.ID;


This is great when you join "Product" table more than once.


You need to be careful using this syntax when the ID column isn't unique in New_Prices. You may end up with the Product table being updated based on the "wrong" (ie unexpected) row from New_Prices. You won't get an error message - it'll just do it.

You can use MERGE instead (SQL Server 2008 onwards), or the ANSI-compliant UPDATE syntax. Please see here for some of the pros and cons.

Please note my intention is not to start another quasi-religious debate on the subject, just to bring these issues to readers' attention.

John

Edit: thanks for the article by the way, Greg. I learned something new about the .WRITE syntax.
Post #1371316
Judy Zingher
Posted Monday, October 15, 2012 8:08 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 31, 2012 6:24 AM
Points: 300, Visits: 15
I found the output clause very timely for my project: thanks! You might want to look at figure 7, though. It is a repeat of figure 6.
Post #1372748
marlon.seton
Posted Wednesday, October 31, 2012 3:47 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: Today @ 2:17 AM
Points: 539, Visits: 183
Re this format which I've never come across before:
"UPDATE Product
SET Price = N.Price
FROM Product P JOIN New_Prices N
ON P.ID = N.ID
WHERE P.ProductName = 'Doo-Dads';"

Does anyone know the Oracle equivalent of this? So far, I've always used this format

"UPDATE Product P
SET P.Price = (SELECT N.Price
FROM New_Prices N
WHERE P.ID = N.ID);"
(script not checked for correctness, it's just an example of the structure)

in both Oracle and SQLServer which is OK for one column but a PITA for more than one column as you have to repeat the SELECT for every column.
Post #1379215
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

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