Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Location
    NJ
    Posts
    2

    Unanswered: DB2 UPDATE statement

    Having come from a SQL Server, Oracle background, I am still trying to figure out differences between DB2/UDB SQL and others.

    I have a SQL Server UPDATE statement which goes like this:

    UPDATE A1
    SET A1.COL1=A2.COL1
    FROM TABLE1 A1, TABLE2 A2
    where A1.COL2 = 'xyz'
    AND A1.COL3=A2.COL3

    ie: I am trying to update ONE row in table TABLE1 (COL2='xyz') .

    DB2 apparently, does not support a FROM or WHERE clause in an UPDATE statements except if it is part of a sub-select.

    So, how should I change this seemingly simple SQL (simple for SQL Server) to accomplish the update in DB2 ?

    Thanks.

  2. #2
    Join Date
    Nov 2002
    Location
    Delaware
    Posts
    186
    Try this,


    update a1 x1
    set ( x1.col1 )
    =
    (select x2.col1
    from x2
    where x1.col2 = 'xyz'
    and x1.col3 = x2.col2)


    My sql is rusty but this is how you would do a subselect with the update command

  3. #3
    Join Date
    Dec 2002
    Location
    NJ
    Posts
    2
    Won't it update all the rows in a1? I want to update only the rows that fit the qualification.

    Thanks.



    Originally posted by quigleyd
    Try this,


    update a1 x1
    set ( x1.col1 )
    =
    (select x2.col1
    from x2
    where x1.col2 = 'xyz'
    and x1.col3 = x2.col2)


    My sql is rusty but this is how you would do a subselect with the update command

  4. #4
    Join Date
    Jan 2002
    Location
    Manila, Philippines
    Posts
    71

    How about this ...

    Update Table1
    Set Table1.Col1=
    (Select Table2.Col1
    from Table2
    where Table1.Col2='xyz'
    and Table1.Col3=Table2.Col3)


    HTH,
    Oliver

  5. #5
    Join Date
    Dec 2002
    Location
    Vancouver, BC
    Posts
    1

    Re: How about this ...

    Try this:

    UPDATE TABLE1 A1
    SET A1.COL1 = (SELECT A2.COL1
    FROM TABLE2 A2
    WHERE A1.COL3=A2.COL3)
    WHERE A1.COL2 = 'xyz'

Posting Permissions

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