February 22, 2012 at 5:25 am
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
October 10, 2012 at 12:34 am
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.
October 10, 2012 at 12:36 am
Good article.
Listing 7 is an incorrect script. It is a repeat of Script 6.
October 10, 2012 at 4:30 am
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
October 10, 2012 at 5:21 am
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.
October 10, 2012 at 7:53 am
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'
October 11, 2012 at 3:50 am
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.
October 15, 2012 at 8:08 am
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.
October 31, 2012 at 3:47 am
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply