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

stairway to T-SQL DML Level 10: Changing Data with the UPDATE Statement Expand / Collapse
Author
Message
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: Friday, June 6, 2014 2:06 PM
Points: 1,040, Visits: 277
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
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, September 15, 2014 2:48 AM
Points: 3, Visits: 102
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
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: Monday, June 30, 2014 2:52 AM
Points: 4, Visits: 58
Good article.

Listing 7 is an incorrect script. It is a repeat of Script 6.
Post #1370709
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: Tuesday, September 16, 2014 8:01 AM
Points: 1,070, Visits: 906
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
Posted Wednesday, October 10, 2012 5:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 7, 2014 6:19 AM
Points: 26, Visits: 163
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
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: Wednesday, August 14, 2013 9:27 AM
Points: 266, Visits: 161
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
Posted Thursday, October 11, 2012 3:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:50 AM
Points: 5,367, Visits: 9,913
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
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
Posted Wednesday, October 31, 2012 3:47 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: Tuesday, April 15, 2014 8:03 AM
Points: 825, Visits: 319
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