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

  • 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

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

  • Good article.

    Listing 7 is an incorrect script. It is a repeat of Script 6.

  • 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

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

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

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

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

  • 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