A double table association update on the self-growth of Rack ()

Table A (tb_abc):

AB1aa

02002bb03003cc05004dd18005ee22006ff3300

Table B (tb_abcc):

A

B1aa

(0201)

2aa(0202)3bb(0301)4bb(0302)5bb(0303)6cc(0501)

Expected values in parentheses.

Rule: Match field a of table A through field a of table B, read field b of table A, and write field b of table B in ascending order according to the value.

Implementation:

update tb_abcc cset c.b = (select tmp.str from (select b.rowid rd, b.a, substr(a.b, 1, 2) || lpad( ( rank () over (partition by b.a order by b.rowid) ), 2, 0 ) str from tb_abc a, tb_abcc b where a.a = b.a) tmp where c.rowid = tmp.rd)where exists (select 'x' from (select b.rowid rd, b.a, substr(a.b, 1, 2) || lpad( ( rank () over (partition by b.a order by b.rowid) ), 2, 0 ) str from tb_abc a, tb_abcc b where a.a = b.a) tmp where c.rowid = tmp.rd);

6 rows updated

select * from tb_abcc;

A B

---- ------

aa 0201

aa 0202

bb 0301

bb 0302

bb 0303

cc 0501

6 rows selected