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

d_lynch · #1 (**permalink**) 12-02-04, 11:07

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?

joebednarz · #2 (**permalink**) 12-02-04, 15:20

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);

r937 · #3 (**permalink**) 12-02-04, 15:30

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 )

d_lynch · #4 (**permalink**) 12-02-04, 15:51

Thanks, I just replaced NVL with Coalesce and it worked fine. I greatly appreciate the help.

joebednarz · #5 (**permalink**) 12-03-04, 00:34

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.

r937 · #6 (**permalink**) 12-03-04, 05:11

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

joebednarz · #7 (**permalink**) 12-03-04, 09:40

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech