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.