Results 1 to 14 of 14

Thread: Update From

  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Update From

    Someone told me recently that UPDATE FROM is proprietary syntax and not the standard.

    So what should be used instead?!

    I'm mainly a Microsoft SQL Server user so my guess is that, as of version 2005, MERGE is the answer?
    What about previous versions?
    George
    Home | Blog

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I forget the strinct standard syntax but I think it is a series of corrolated subselects. Try googling Celko on this - he is a strong proponent of the standard update syntax.

    HTH until someone more standards literate than lil' ol' me comes along.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Blimey - check out all the luminaries in here for example:
    http://www.eggheadcafe.com/software/...-sql-help.aspx
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Wow, what a complicated premise... Let's see if I understand this:
    Code:
    USE playdb
    
    CREATE TABLE table_1 (
       some_id     int         PRIMARY KEY
     , some_value  varchar(10)
     , other_value varchar(10)
    )
    
    CREATE TABLE table_2 (
       some_id     int         PRIMARY KEY
     , some_value  varchar(10)
     , other_value varchar(10)
    )
    
    SET NOCOUNT ON
      INSERT INTO table_1 (some_id, some_value, other_value)
            SELECT 1, NULL, 'a'
      UNION SELECT 2, NULL, 'b'
      UNION SELECT 3, NULL, 'c'
    
      INSERT INTO table_2 (some_id, some_value, other_value)
            SELECT 1, 'row 1', 'x'
      UNION SELECT 2, 'row 2', 'y'
      UNION SELECT 3, 'row 3', 'z'
    SET NOCOUNT OFF
    
    --SELECT * FROM table_1
    --SELECT * FROM table_2
    
    BEGIN TRAN --Proprietary UPDATE FROM syntax
      UPDATE table_1
      SET    some_value = t2.some_value
           , other_value = t2.other_value
      FROM   table_1 As [t1]
       INNER
        JOIN table_2 As [t2]
          ON t1.some_id = t2.some_id
    
      SELECT * FROM table_1
    ROLLBACK TRAN
    
    BEGIN TRAN --Still a FROM clause..?
      UPDATE table_1
      SET    some_value  = (SELECT some_value  FROM table_2 t2 WHERE t1.some_id = t2.some_id)
           , other_value = (SELECT other_value FROM table_2 t2 WHERE t1.some_id = t2.some_id)
      FROM   table_1 t1
    
      SELECT * FROM table_1
    ROLLBACK TRAN
    
    GO
    DROP TABLE table_1
    DROP TABLE table_2
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I can't believe you used proprietary insert syntax too!!!1!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - I believe this:
    Code:
    ......
    BEGIN TRAN --Still a FROM clause..?
      UPDATE table_1
      SET    some_value  = (SELECT some_value  FROM table_2 t2 WHERE t1.some_id = t2.some_id)
           , other_value = (SELECT other_value FROM table_2 t2 WHERE t1.some_id = t2.some_id)
      FROM   table_1 t1
    should not include the bit in red to be ANSItastic.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by georgev
    Wow, what a complicated premise... Let's see if I understand this:
    Code:
    USE playdb
    There is no "USE" statement in standard SQL. So this is proprietary already.

    Code:
    SET NOCOUNT ON
      INSERT INTO table_1 (some_id, some_value, other_value)
            SELECT 1, NULL, 'a'
      UNION SELECT 2, NULL, 'b'
      UNION SELECT 3, NULL, 'c'
    I have no idea what this SET NOCOUNT ON should do. It isn't standard SQL. Also, statement terminators would be helpful to understand the code better (which is typically a good thing).

    The INSERT statement itself doesn't make any sense. You have there "SELECT 1, NULL, 'a'" - select this FROM what? There is the FROM clause missing. You could simplify the multi-row insert to this:
    Code:
    INSERT INTO table_1 (some_id, some_value, other_value)
    VALUES (1, NULL, 'a'),
           (2, NULL, 'b'),
           (3, NULL, 'c')
    Code:
      UPDATE table_1
      SET    some_value = t2.some_value
           , other_value = t2.other_value
      FROM   table_1 As [t1]
       INNER
        JOIN table_2 As [t2]
          ON t1.some_id = t2.some_id
    First, I would remove the brackets around the tables' correlation names because they don't belong there.

    Now let's try to read the statement (which is one of the things you can typically do with SQL):
    Update "table_1" and set the value in columns "some_value" and "other_value".

    Here we start stumbling: Now comes a "FROM" - it simply doesn't fit in the sentence, wouldn't you agree? Even more strange from a logical point of view is that "table_1" occurs in this FROM clause again.

    What is more natural and straight-forward would be the following. You define which table shall be updated, calculate the new values and finally you restrict the rows on which the update shall take place.
    Code:
    UPDATE table_1 AS t1
    SET    ( some_value, other_value ) = ( SELECT t2.some_value, other_value
                                           FROM table_2 AS t2
                                           WHERE t1.some_id = t2.some_id )
    WHERE EXISTS ( SELECT 1
                   FROM table_2 AS t2
                   WHERE t1.some_id = t2.some_id )
    Some other extensions (which may find their way into the standard) is this:
    Code:
    UPDATE ( SELECT t1.some_value, t1.other_value, t2.some_value AS new_some_value, t2.other_value AS new_other_value
             FROM table_1 AS t1 JOIN table_2 AS t2 ON WHERE t1.some_id = t2.some_id )
    SET some_value = new_some_value, other_value = new_other_value
    The DBMS builds a temporary table that does the join, then applies the update on this temporary table and maps it back to the underlying base tables or views. Of course, this will only work if such a mapping-back can be done in the first place and the columns being updated are updateable.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Happier syntax?
    Code:
    CREATE TABLE table_1 (
       some_id     int         PRIMARY KEY
     , some_value  varchar(10)
     , other_value varchar(10)
    );
    
    CREATE TABLE table_2 (
       some_id     int         PRIMARY KEY
     , some_value  varchar(10)
     , other_value varchar(10)
    );
    
    INSERT INTO table_1 (some_id, some_value, other_value)
    VALUES (1, NULL, 'a');
    INSERT INTO table_1 (some_id, some_value, other_value)
    VALUES (2, NULL, 'b');
    INSERT INTO table_1 (some_id, some_value, other_value)
    VALUES (3, NULL, 'c');
    
    INSERT INTO table_2 (some_id, some_value, other_value)
    VALUES (1, 'row 1', 'x');
    INSERT INTO table_2 (some_id, some_value, other_value)
    VALUES (2, 'row 2', 'y');
    INSERT INTO table_2 (some_id, some_value, other_value)
    VALUES (3, 'row 3', 'z');
    
    SELECT * FROM table_1;
    SELECT * FROM table_2;
    
    GO
    DROP TABLE table_1;
    DROP TABLE table_2;
    I'm still struggling to get my head round the update logic - but this is probably down to the FROm method being embedded in my memory!

    I'll give this a more thorough read later today, but I have a couple of minor questions that are somewhat unrelated from the UPDATE syntax.
    Quote Originally Posted by stolze
    I would remove the brackets around the tables' correlation names because they don't belong there.
    Why should they be removed, I appreciate that they are not needed in this instance, is that the only reason or am I missing something?

    Now this may be a large leap in logic, but I predict that the standard method over large resultsets, especially when updating more than one column at a time, could be less efficient than the prorietary method - is this assumption near true?
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Stolze - I'm like George and predominantly use MS T-SQL and know little of the standard. Is this standard SQL?
    Code:
    UPDATE table_1 AS t1
    SET    ( some_value, other_value ) = ( SELECT t2.some_value, other_value
                                           FROM table_2 AS t2
                                           WHERE t1.some_id = t2.some_id )
    ???
    If so then the infamous UPDATE....FROM would be totally unnecessary if T-SQL supported the standard fully.

    In case you are interested (bet you ain't) SET NOCOUNT ON just tells SQL Server not to count, and return, the number of rows affected by the proceeding statements.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by pootle flump
    Stolze - I'm like George and predominantly use MS T-SQL and know little of the standard. Is this standard SQL?
    Code:
    UPDATE table_1 AS t1
    SET    ( some_value, other_value ) = ( SELECT t2.some_value, other_value
                                           FROM table_2 AS t2
                                           WHERE t1.some_id = t2.some_id )
    ???
    Yes, this is standard SQL.

    If so then the infamous UPDATE....FROM would be totally unnecessary if T-SQL supported the standard fully.
    Updating multiple values as shown above is a bit independent from what UPDATE ... FROM tries to achieve. UPDATE ... FROM tries to avoid the necessity to specify the same subselect twice: once in the SET clause and once in the WHERE clause of an update statement. However, (and that answers georgev's question, too) any decent system detects that the same subselect has been used there and only executes it once. So the argument of being less efficient doesn't hold any water.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by georgev
    Happier syntax?
    [CODE]
    INSERT INTO table_1 (some_id, some_value, other_value)
    VALUES (1, NULL, 'a');
    INSERT INTO table_1 (some_id, some_value, other_value)
    VALUES (2, NULL, 'b');
    INSERT INTO table_1 (some_id, some_value, other_value)
    VALUES (3, NULL, 'c');
    Yes, but too excessive executing 3 statements instead of just 1 ;-)

    And the "GO" is also not standard SQL ;-)

    Why should they be removed, I appreciate that they are not needed in this instance, is that the only reason or am I missing something?
    It's not standard SQL. A <correlation name> in a <from clause> is an <identifier>. What identifiers are is defined in subclause 5.4, "Names and identifiers" in the SQL standard (I'm looking at SQL:2003). There, an identifier is either a <regular identifier>, a <delimited identifier>, or a <unicode delimited identifier>. All delimited identifiers have to have double-quotes around them and unicode delimited identifiers have a leading 'U'. Only with delimited identifiers you could have a character like '[' in the identifier name. Since you don't have double-quotes there, it must be a regular identifier, in which case the first character must be one of (cf. Note 58 in SQL:2003 in subclause 5.2):
    upper-case letters, lower-case letters, title-case letters, modifier letters, other letters, and letter numbers
    Only letters and numbers are mentioned there. Thus, the brackets are not part of the identifier (and you didn't use it as such). But the standard syntax says:
    <table or query name> [ [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ] ]
    The ']' indicate optional arguments in the BNF notation and not characters that could by typed in. Thus, my take on this is that using "table_1 AS [t1]" is a proprietary product extension of T-SQL only.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by stolze
    Thus, my take on this is that using "table_1 AS [t1]" is a proprietary product extension of T-SQL only.
    ding ding ding ding ding

    we have a winnah!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by georgev
    I'm mainly a Microsoft SQL Server user so my guess is that, as of version 2005, MERGE is the answer?
    What about previous versions?
    MERGE is the answer as from SQL Server 2008. It ought to make the atrocious and flawed UPDATE ... FROM obsolete. True, MERGE has a lousy syntax but it avoids the problems of Microsoft's UPDATE and in many cases MERGE is more efficient than the equivalent UPDATE ... FROM.

  14. #14
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    MERGE is standard SQL, too. ;-)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •