Click here to monitor SSC
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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1297 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
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 100
Good article.

Listing 7 is an incorrect script. It is a repeat of Script 6.
RichB
RichB
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1335 Visits: 1035
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



INCREDIBLEmouse
INCREDIBLEmouse
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 240
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 Veteran
SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)

Group: General Forum Members
Points: 296 Visits: 167
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
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11879 Visits: 15704
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 (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)

Group: General Forum Members
Points: 306 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
SSC Eights!
SSC Eights! (873 reputation)SSC Eights! (873 reputation)SSC Eights! (873 reputation)SSC Eights! (873 reputation)SSC Eights! (873 reputation)SSC Eights! (873 reputation)SSC Eights! (873 reputation)SSC Eights! (873 reputation)

Group: General Forum Members
Points: 873 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