update values based on values from another table

All we need is an easy explanation of the problem, so here it is.

I have a table (Table A) in DB2 where a columns needs to be updated based on values from values from Table B.

e.g.

Table A has two columns and following data.

 ID    LOGIN
 01    DONALD.TRUMP
 02    BARACK.OBAMA
 03    ABRAHAM.LINCOLN

Table B has the following data.

 ID    OLDLOGIN            NEWLOGIN
 01    DONALD.TRUMP        DONALD.T
 02    ABRAHAM.LINCOLN     ABRAHAM.LIN

I want to update Table A’s LOGIN value, give them the NEWLOGIN values if they exist in OLDLOGIN in Table B.

So the final result in Table A should be like below

 ID    LOGIN
 01    DONALD.T
 02    BARACK.OBAMA
 03    ABRAHAM.LIN

What would be the easiest way to do that?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I assume LUW and a recently new one. Probably clearest to do with a MERGE statement like:

MERGE INTO TA
USING (
    SELECT OLDLOGIN, NEWLOGIN
    FROM TB
) AS TB
    ON TA.LOGIN = TB.OLDLOGIN
WHEN MATCHED THEN
    UPDATE SET LOGIN = NEWLOGIN ;

You can also do it with a traditional UPDATE statement:

UPDATE TA
    SET LOGIN = ( SELECT NEWLOGIN 
                  FROM TB 
                  WHERE OLDLOGIN = TA.LOGIN )
WHERE EXISTS (
    SELECT 1 
    FROM TB
    WHERE OLDLOGIN = TA.LOGIN 
);

Method 2

try this:

update tablea f1
set f1.login=
(
    select f2.newlogin from tableb f2
    where f1.oldlogin=f2.newlogin
    fetch first rows only
)
where exists
(
    select * from tableb f2
    where f1.oldlogin=f2.newlogin
)

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply