Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2004
    Posts
    2

    Unanswered: [DB2] Update Field in Table A to a Field in Table B

    I am trying to change the values in Field2 of table A to the values in Field2 of table B wherever the tables have the same value for Field1.

    This works in Microsoft Access, but I can't figure out how to make it work in DB2.

    UPDATE A INNER JOIN B ON A.Field1 = B.Field1 SET A.Field2 = [B].[Field2];

    Any ideas?
    Last edited by d_lynch; 12-02-04 at 12:12.

  2. #2
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    I know this works in Oracle, but not tested in DB2...

    Code:
    UPDATE a
    SET a.field2 = NVL( ( SELECT b.field2
                                 FROM b
                                 WHERE b.field1 = a.field1), a.field2);
    JoeB
    save disk space, use smaller fonts

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this (untested; i don't have DB2, but i know it allows scalar subqueries in the UPDATE statement) --
    Code:
    update A 
       set Field2 
         = ( select Field2
               from B
              where Field1 = A.Field1 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Dec 2004
    Posts
    2
    Thanks, I just replaced NVL with Coalesce and it worked fine. I greatly appreciate the help.

  5. #5
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Rudy,

    Just so you know... I tried your variation in an attempt to find a solution for d_lynch... problem I found was that:

    Code:
    update A 
       set Field2 
         = ( select Field2
               from B
              where Field1 = A.Field1 )
    ...works for the fields that have a match, however, if there is no match, whatever was in A.FIELD2 is now replaced with a NULL.
    JoeB
    save disk space, use smaller fonts

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks, joe, i understand that

    i wouldn't update A.Field2 with itself, though -- could be lotsa useless log activity

    i'd use a WHERE clause to ensure that only those rows which had a match are actually updated
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Cool... not really into correcting other people's code, but had tried it so I thought I'd mention the results. BTW, always appreciate your answers to questions... very well thought out.
    JoeB
    save disk space, use smaller fonts

Posting Permissions

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