Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Unanswered: UPDATE a set of columns

    In DB2 I could write
    Code:
    UPDATE myTAble
    SET (column1, column2, column3) = 
              (SELECT tableX.columnX, 
                      tableY.columnY,  
                      tableZ.columnZ 
              FROM ..... (complex join follows) ...
              )
    In SQL Server 2005 I get an error. Is there a way to do this, so this complex time consuming query only has to run once?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    UPDATE myT
    SET col1 = x.col1
    , col2 = x.col2
    FROM myT
    INNER JOIN 
    x
    ON ....
    Watch it though - it upsets Celko
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Really? What problem does he have with that? It always narks me off when I'm forced to write PSQL for Oracle and can't use that syntax.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It's not standard. I believe he advocates:
    Code:
    UPDATE myT
    SET col1 = (SELECT col1 from x WHERE x.pk = myT.pk)
    , col2 = (SELECT col1 from x WHERE x.pk = myT.pk)
    .....
    This is standard, but much less efficient (assuming the engine doesn't figure out that it can scan x just once - I've never tested).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Microsoft's proprietary UPDATE FROM syntax should come with a stern health warning. It has a dreadful bug-feature that causes random results if the join criteria isn't unique. Instead of issuing an error message, which is what common sense would dictate, it will silently overwrite your data with an arbitrary result.

    Aside from that, the syntax is non-standard, incompatible with just about everything (including at least two other Microsoft products - Access and FoxPro) and downright ugly and confusing.

    Fortunately, the introduction of the MERGE statement corrects all those problems. In SQL Server 2008 and later don't use UPDATE ... FROM at all. Use MERGE instead because it has numerous advantages.

    UPDATE ... FROM is well overdue to be deprecated or removed altogether. There is a MS Connect suggestion to that effect so please vote for it!

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Thank you all for your replies.

    I'll see how MERGE could prove a solution.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I disagree, Portas.
    I think that syntax rocks. Its simple and intuitive.
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I disagree.

    I dislike all diversions from the SQL standard. Why would MS want to introduce its own incompatible set of rules? Whatever the real reason behind this is, it will surely not be to make life easier for us.

    I agree if MS (or any other company) would introduce a non-standard SQL addition if it fills a gap, but not when it is already included in the SQL standard.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Nah - extensions are fine. You want to generate your own surrogates? You want to generate your own sequential GUIDs?

    You forget as well that this was syntax available in SQL Server many years before Merge. You are of course free, Wim, to write a subquery for each and every column you wish to update, as per the pre-MERGE standard
    Last edited by pootle flump; 05-07-09 at 12:51.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Wim
    I dislike all diversions from the SQL standard.
    so you prefer string1 || string2 instead of SQL Server's string1 + string2, right?

    good for you, i love a guy who follows his principles and uses only the Mimer dbms

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Wim
    I dislike all diversions from the SQL standard. Why would MS want to introduce its own incompatible set of rules?
    The FROM syntax within the UPDATE statement was introduced before any SQL standard was officially supported by the ISO. At that time, ANSI still maintained the definition of the SQL standard and the FROM clause was considered for the proposed ANSI SQL-89 standard but other vendors (notably IBM and Oracle) voted it down.

    MERGE is consistant with the current standard, and logically it makes a lot more sense

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

  12. #12
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by r937
    so you prefer string1 || string2 instead of SQL Server's string1 + string2, right?
    || or +, what is the big deal? I have used || for years. AFAIK, no ill effects have occurred to me, and besides that second head is useful to eat while I'm drinking. And I admit, + is more natural than || to glue strings together.
    Quote Originally Posted by pootle flump
    You forget as well that this was syntax available in SQL Server many years before Merge.
    As I stated before "I agree if MS (or any other company) would introduce a non-standard SQL addition if it fills a gap, but not when it is already included in the SQL standard." So it was definitely to fill a gap, at the time it was created. I do regret that the SQL-standard compliant form is not implemented.
    Quote Originally Posted by pootle flump
    You are of course free, Wim, to write a subquery for each and every column you wish to update, as per the pre-MERGE standard
    Its because I wanted to avoid this that I opened this thread.

    Peace.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Wim
    |I have used || for years. AFAIK, no ill effects have occurred to me...
    A slippery slope. Next, you'll be wanting to legalize marijuana.
    If it's not practically useful, then it's practically useless.

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

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I guess that this falls back to the unpopular adage, if you want new toys then you have to upgrade to the new toys. If you upgrade to SQL 2008, you can use MERGE and be completely ISO compliant (which is what I'd recommend). If you choose to stay with SQL 2005, then you have to work within the limitations of its database engine and you have to work around the lack of the MERGE statement.

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

Posting Permissions

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