Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12

    Unanswered: Update with a JOIN

    In the last weeks I came to work with SQL Server more closely and - not being used to it - I stumbled over the sematics of an UPDATE statement using a JOIN (something which is not available in e.g. Oracle).

    I wonder what the difference between these two updates is:

    Code:
    update foo 
       set ..
    from bar 
      where bar.fid = foo.id;
    and
    Code:
    update foo
       set ...
    from foo f1
      join bar on bar.fid = f1.id;
    In both cases I have an inner join between foo and bar, but in the second one, foo is actually listed twice in the update statement.

    As far as I can tell, both carry out the same thing - at least with my test data.

    So my question is: is there a difference between the two, and if yes, what exactly is the difference?
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  2. #2
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Do not use this!!

    This is an old 1970's Sybase syntax. It does not work because it has cardinality problems. MVPs are begging to have it deprecated. Use teh ANSI/IOSO Standard MERGE statement. It works, it ports

    MERGE INTO Foo
    USING Bar
    ON Bar.generic_id = Foo.generic_id
    WHEN MATCHED
    THEN UPDATE
    SET ...;

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    I'm forced to use SQL Server 2005, so I don't have MERGE available.

    And I would really like to know if there is a difference between the two versions.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If the MERGE statement existed in SQL 2005, I'd recommend using it too. Since MERGE isn't a choice in SQL 2005, we have to use another method.

    In the case of changing a single column in foo using a computation from bar, I recommend using a subquery because it is portable to other SQL dialects. Something like:
    Code:
    UPDATE foo
       SET bat = (SELECT baz
          FROM bar
          WHERE  bar.fly = foo.fly)
    When you need to update multiple columns in foo, then I prefer to use the UPDATE ... FROM ... JOIN syntax. Something like:
    Code:
    UPDATE f
       SET bat = b.baz
    ,     qux = b.quix
       FROM foo AS f
       JOIN bar AS b
          ON (b.fly = f.fly)
    Note that I did not use UPDATE foo, because that would have introduced another alias and probably a Cartesian Join into the UPDATE statement!

    I tend to like the original Sybase syntax from Shammat's first example because it is familiar and that makes it comfortable for me. It is logically consistant, avoids the spurious alias problem, but was vehemently rejected by the standards committee years ago. I'm sure that there must be something dreadfully wrong with:
    Code:
    update foo 
       set ..
    from bar 
      where bar.fid = foo.id;
    ...but I'll be dipped if I can figure out what the offensive part may be!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    There is no difference between those two statements.
    I prefer the 2nd for clarity because it's easily converted to a SELECT for testing purposes, and consistency because more complex UPDATE statements may require it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Pat Phelan View Post
    Note that I did not use UPDATE foo, because that would have introduced another alias and probably a Cartesian Join into the UPDATE statement!
    No, you don't need an alias for this:
    Code:
    UPDATE foo
       SET bat = bar.baz
    ,     qux = bar.quix
       FROM foo
       JOIN bar
          ON (bar.fly = foo.fly)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman View Post
    No, you don't need an alias for this:
    Code:
    UPDATE foo
       SET bat = bar.baz
    ,     qux = bar.quix
       FROM foo
       JOIN bar
          ON (bar.fly = foo.fly)
    Check Shammat's original post... There was an alias in the original code and I wasn't willing to change that to suit my expectations without comment. I chose to provide what I saw as the simpler answer.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Good catch on that. I'm not sure what the use of an alias in the SELECT clause without repeating the alias in the UPDATE clause would do. Worst case, it would update every record with some random value....
    Code:
    update foo
       set ...
    from foo f1
      join bar on bar.fid = f1.id;
    ...best case, it would throw an error.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    I am sorry for your hardships ..

    I'm forced to use SQL Server 2005, so I don't have MERGE available.
    I am sorry for your hardships , but write the MERGE and put it in as a comment. This is why people like to hire me . When I leave and they have to port my code to a new release or another SQL, they look at my comments and see if they are in "ANSI/ISO Standard Land" yet. I have a good story about *= versus LEFT OUTER JOIN.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Joe:

    Sometime between "Let there be light..." and now the ANSI committee had a fit about the Sybase UPDATE ... FROM syntax. I'm pretty sure that you were sitting on the committee at that time. Can you shed any light on why the committee was so vehemently opposed to what still seems like a good idea to me?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Thanks for all the answers. I think I understand it better now.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

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