[SOLVED] Update two table with double rows

Update two table with double rows

(ORACLE) Dear, I have tbA :

ID	map
1
2
2
2
2
3
3
4
5

(1.5 million rows!!) Id 1, 2, 3 is example (it's contain a-z… not only number-can not use min max)

tbB :

ID	map
2
2
2
3
3
3
5

(1.2 million rows)

And I want update value on tbA and tbB

tbA :

ID	map
1
2	ok
2	ok
2	ok
2
3	ok
3	ok
4
5	ok

tbB :

ID	map
2	ok
2	ok
2	ok
3	ok
3	ok
3
5	ok

Please help me SQL to update it.

I plan to numbering appearance:

ID	map	App
1		1
2		1
2		2
2		3
2		4
3		1
3		2
4		1
5		1

And get key = id & app to compare but how to? Or please help me SQL to update it. Note: table has many rows (>1m rows)

Please help me, Thanks so much!

Solution

Match using the ID column and the ROW_NUMBER analytic function to ensure each ID has a unique incrementing value:

UPDATE tba
SET map = 'ok'
WHERE ROWID IN (
  SELECT a.rid
  FROM   (
           SELECT ROWID AS rid,
                  id,
                  ROW_NUMBER() OVER (PARTITION BY id ORDER BY ROWNUM) AS rn
           FROM   tba
         ) a
         INNER JOIN (
           SELECT id,
                  ROW_NUMBER() OVER (PARTITION BY id ORDER BY ROWNUM) AS rn
           FROM   tbb
         ) b
         ON a.id = b.id AND a.rn = b.rn
);

For tbb then swap tba and tbb and the corresponding aliases wherever they occur.

fiddle