sql - DB2 Copy one column values to another column

Question

In DB2, I have 2 tables - TableA and TableB. In Table A, there are 10 rows. In Table B, there are 12 rows. Table A has a column named IntValueA with Integer type. Table B has a column named IntValueB with Integer type.

I want TableA's IntValueA to replace any 10 TableB's IntValueB values, such that, TableA and TableB will have 10 common values where IntValueA=IntValueB.

IntValueA can replace ANY of IntValueB. No condition.

Can someone please help me how the DB2 query should look like?

question from:https://stackoverflow.com/questions/66054296/db2-copy-one-column-values-to-another-column

Answer 1

Try this.

UPDATE 
(SELECT IntValueB, ROWNUMBER() OVER() RN_ FROM TableB) B
SET IntValueB = 
(
SELECT IntValueA 
FROM (SELECT IntValueA, ROWNUMBER() OVER() RN_ FROM TableA) A
WHERE A.RN_ = B.RN_
)
WHERE RN_ <= 10;