I have a situation where all the data in a column of Table2 needs to be migrated to another column of Table1 based on a join condition.

For example,

Table1 has A,B,C

and

Table2 has X,Y,Z. ( Y foreign key to A of Table1)

I add a new column Z to Table1 and need all the Table2 Z values in Table1 Z column where Table1.A = Table2.Y

How can I do this in Db2 via SQL, Stored Procs or any other DB2 tools?

I tried Insert statements such as follows ,. but does not work

Insert into Table1(Z)
select Table2.Z from Table1,Table2 where Table1.A = Table2.Y

Then, if multiple rows of Table2 matched with a row of Table1,
what to do?

Here is an exmple,
if multiple rows of Table2 matched with a row of Table1,
choose one by some additional conditions.

For example: Take maximum value.
(Not tested)