• 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.