[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?I know this works in Oracle, but not tested in DB2...

UPDATE a
SET a.field2 = NVL( ( SELECT b.field2
FROM b
WHERE b.field1 = a.field1), a.field2);|||try this (untested; i don't have DB2, but i know it allows scalar subqueries in the UPDATE statement) --update A
set Field2
= ( select Field2
from B
where Field1 = A.Field1 )|||Thanks, I just replaced NVL with Coalesce and it worked fine. I greatly appreciate the help.|||Rudy,

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

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.|||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|||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.