Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2008
    Posts
    8

    Unanswered: DB2 Update with Select inner join

    Hi All,

    I am trying to write an update query by joining a temporary session table (with some calculated values) along with Table A but getting "Unexpected token found Begin of statement"

    update Table A
    set A.Ind=B.Ind
    from Table A inner join session.Table B
    on A.ID=B.ID and A.rank=B.rank

    I tried with the following syntax too but it is giving me error as "Unexpected token from was found"

    update Table A
    set A.Ind=B.Ind
    from Table A,session.Table B
    where A.ID=B.ID and A.rank=B.rank

    Have tried using the above syntax in SQL Server before. Not sure what is missing.

    Thank you in advance for your valuable suggestion.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Microsoft SQL Server supports the FROM clause in an UPDATE statement because it descends from an earlier SQL dialect that used to support it. Very few SQL dialects support the FROM clause within the UPDATE statement.

    The ISO standard way to do this is:
    Code:
    UPDATE tableA
       SET Ind = (SELECT b.Ind
          FROM tableB
          WHERE  tableB.ID = tableA.ID
             AND tableB.Rank = tableA.Rank)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2008
    Posts
    8
    Quote Originally Posted by Pat Phelan View Post
    Microsoft SQL Server supports the FROM clause in an UPDATE statement because it descends from an earlier SQL dialect that used to support it. Very few SQL dialects support the FROM clause within the UPDATE statement.

    The ISO standard way to do this is:
    Code:
    UPDATE tableA
       SET Ind = (SELECT b.Ind
          FROM tableB
          WHERE  tableB.ID = tableA.ID
             AND tableB.Rank = tableA.Rank)
    -PatP

    Thanks Pat. It works but it updates all the records in table A even if there is no matching record in table B which worries me. For the non-matching records it updates null. Do we need to put an exist clause at the end. Please advise.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It seems like you're looking to use much of the power of the Microsoft UPDATE statement extensions. I'd suggest using the DB2 MERGE statement which does all of what you've described and more, and is also ISO standard.

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

  5. #5
    Join Date
    Jun 2008
    Posts
    8
    Quote Originally Posted by Pat Phelan View Post
    It seems like you're looking to use much of the power of the Microsoft UPDATE statement extensions. I'd suggest using the DB2 MERGE statement which does all of what you've described and more, and is also ISO standard.

    -PatP

    Thank you so much Pat. The merge query is working fine. I thought merge would update all the columns in the table. My understanding was wrong. You saved my day. Thank you once again.

  6. #6
    Join Date
    Jun 2008
    Posts
    8
    Quote Originally Posted by Pat Phelan View Post
    It seems like you're looking to use much of the power of the Microsoft UPDATE statement extensions. I'd suggest using the DB2 MERGE statement which does all of what you've described and more, and is also ISO standard.

    -PatP
    Thank you so much Pat. The merge query is working fine. I thought merge would update all the columns in the table. My understanding was wrong. You saved my day. Thank you once again.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by athineethi View Post
    Thanks Pat. It works but it updates all the records in table A
    May be it's because there is no WHERE clause in the UPDATE statement?

    Code:
    UPDATE tableA
       SET Ind = (SELECT b.Ind
          FROM tableB
          WHERE  tableB.ID = tableA.ID
             AND tableB.Rank = tableA.Rank)
    WHERE EXISTS (SELECT 1
          FROM tableB
          WHERE  tableB.ID = tableA.ID
             AND tableB.Rank = tableA.Rank)
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Jun 2008
    Posts
    8
    Thank you so much Pat. The merge query is working fine. I thought merge would update all the columns in the table. My understanding was wrong. You saved my day. Thank you once again.

  9. #9
    Join Date
    Jun 2008
    Posts
    8
    Quote Originally Posted by Pat Phelan View Post
    It seems like you're looking to use much of the power of the Microsoft UPDATE statement extensions. I'd suggest using the DB2 MERGE statement which does all of what you've described and more, and is also ISO standard.

    -PatP
    Thanks n_i. The update sql with where clause works too but I think merge is a better and a safe option to use in DB2.

  10. #10
    Join Date
    Jun 2008
    Posts
    8
    Thanks n_i. The update sql with where clause works too but I think merge is a better and a safe option to use in DB2.

Posting Permissions

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