I have two Oracle tables with different columns but one. I just want to update the common column of one table using the same column from another table based on the same row number like this:
table1:
cst_id | item | price
-----------------------
10 | book | 20
20 | copy | 30
30 | pen | 10
25 | tea | 14
35 | glass| 49
table2:
cst_id | code | color | label | age
-------------------------------------
110 | sss | blue | R | 10
220 | cchh | green | H | 3
330 | eee | yellow| J | 5
448 | yyy | - | U | 4
777 | TTT | white | P | 2
898 | RRR | - | E | 9
997 | GGG | - | W | 4
After updating table1:
cst_id | item | price
-----------------------
110 | book | 20
220 | copy | 30
330 | pen | 10
448 | tea | 14
777 | glass| 49
I use this query:
MERGE INTO table1 t1
USING (
SELECT cst_id, ROWID as rid
FROM (
SELECT cst_id, ROW_NUMBER() OVER (ORDER BY age) AS rn
FROM table2
) t2
) t2
ON (t1.ROWID = t2.rid)
WHEN MATCHED THEN
UPDATE SET t1.cst_id = t2.cst_id;
But the result is 0 row merge.
Is there another way to do this update?
The theoretical result you present is not correlated to the query you wrote: if you order table2 by age, the first row will be the 777 one and thus the result will be "777 | book | 20" not what you present. You will not be able to do it in SQL with MERGE (error you can get is "ORA-01445: Cannot Select ROWID from a Join View without a Key-Preserved Table") but in PL/SQL you can do:
begin
FOR rec IN (
SELECT t2.cst_id, t2.rn, t1.rid FROM
(SELECT cst_id, ROW_NUMBER() OVER (ORDER BY age) AS rn FROM table2) t2
JOIN (SELECT rowid AS rid, ROW_NUMBER() OVER (ORDER BY cst_id) AS rn FROM table1) t1
ON t1.rn = t2.rn
)
LOOP
UPDATE table1 SET cst_id = rec.cst_id
WHERE rowid = rec.rid;
END LOOP ;
end ;
And the result will be:
777 book 20
220 copy 30
997 pen 10
448 tea 14
330 glass 49