SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


stairway to T-SQL DML Level 10: Changing Data with the UPDATE Statement


stairway to T-SQL DML Level 10: Changing Data with the UPDATE Statement

Author
Message
Greg Larsen
Greg Larsen
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15143 Visits: 290
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
Mr.DiGi
Mr.DiGi
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 133
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.
simon.duckett
simon.duckett
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 103
Good article.

Listing 7 is an incorrect script. It is a repeat of Script 6.
RichB
RichB
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8177 Visits: 1088
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



Incredible Mouse
Incredible Mouse
SSChasing Mays
SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)

Group: General Forum Members
Points: 659 Visits: 246
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.
gorthog
gorthog
SSC Eights!
SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)

Group: General Forum Members
Points: 834 Visits: 168
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'


John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)

Group: General Forum Members
Points: 122082 Visits: 18761
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.
Judy Zingher
Judy Zingher
Old Hand
Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)

Group: General Forum Members
Points: 320 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.
marlon.seton
marlon.seton
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2229 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search