Requirement in Oracle PL/SQL to update labels-CodePudding

I have 2 tables - tab1 , tab2 with following data

tab1 data:
OID   Label
1     MX1
1     MX2
1     MX3
2     MX4
2     MX5


tab2 data:
OID  ID    Label
1    5678
1    2347
1    9687
2    4567
2    3455

The join condition between these two tables is oid column.I need to create a process which will update Label column from tab1 to Label column of tab2.It doesn't matter which label gets assigned to which record of tab2 for a particular oid. The only check that needs to happen is that both the tables should have same number of records for an oid.The final outcome should be the following

tab2 data:
OID  ID    Label
1    5678  MX1
1    2347  MX2
1    9687  MX3
2    4567  MX4
2    3455  MX5

Again, it doesn't matter which label gets assigned to tab2 for a particular oid,but the same label cannot be repeated for a particular oid.What would be the best way to write a code for this requirement?

CodePudding user response：

Here is a sql solution:

merge into tab2 
using 
           (
            select t2."id" as ide,t1."label" labela from
            (select rownum n,"label","oid" from tab1 order by "oid")t1,
            (select rownum n, a2.* from tab2 a2 order by "oid")t2
            where t1.n=t2.n and
                  t1."oid"=t2."oid"
            ) tb4
on (tab2."id" = tb4.ide)
            when matched then 
                update set tab2."label" = tb4.labela;

Result:

oid| id | label
-----------------
1   5678    mx1
1   2347    mx2
1   9687    mx3
2   4567    mx4
2   3455    mx5

CodePudding user response：

Sample tables:

SQL> select * from tab1 order by oid, label;

       OID LAB
---------- ---
         1 mx1
         1 mx2
         1 mx3
         2 mx4
         2 mx5

SQL> select * from tab2 order by oid, id;

       OID         ID LAB
---------- ---------- ---
         1       2347
         1       5678
         1       9687
         2       3455
         2       4567

SQL>

This is query that returns desired result:

SQL> with
  2  t1 as (select oid, label, rowid rwid,
  3           row_number() over (partition by oid order by label) rn
  4         from tab1
  5        ),
  6  t2 as (select oid, id, rowid rwid,
  7           row_number() over (partition by oid order by id) rn
  8         from tab2
  9        )
 10  select b.oid, b.id, a.label
 11  from t1 a join t2 b on a.oid = b.oid and a.rn = b.rn;

       OID         ID LAB
---------- ---------- ---
         1       2347 mx1
         1       5678 mx2
         1       9687 mx3
         2       3455 mx4
         2       4567 mx5

SQL>

A few options I tried: correlated update won't work because of

ORA-01779: cannot modify a column which maps to a non key-preserved table

SQL> update (
  2  with
  3  t1 as (select oid, label, rowid rwid,
  4           row_number() over (partition by oid order by label) rn
  5         from tab1
  6        ),
  7  t2 as (select oid, id, rowid rwid, label,
  8           row_number() over (partition by oid order by id) rn
  9         from tab2
 10        )
 11  select b.oid, b.id, b.label b_label, a.label a_label
 12  from t1 a join t2 b on a.oid = b.oid and a.rn = b.rn
 13  )
 14  set b_label = a_label;
set b_label = a_label
    *
ERROR at line 14:
ORA-01779: cannot modify a column which maps to a non key-preserved table


SQL>

MERGE won't work because of

ORA-01732: data manipulation operation not legal on this view

SQL> merge into
  2           (select oid, id, label, row_Number() over (partition by oid order by id   ) rn from tab2) b
  3    using  (select oid,     label, row_number() over (partition by oid order by label) rn from tab1) a
  4    on (a.oid = b.oid and
  5        a.rn  = b.rn)
  6    when matched then update set
  7      b.label = a.label;
         (select oid, id, label, row_Number() over (partition by oid order by id   ) rn from tab2) b
         *
ERROR at line 2:
ORA-01732: data manipulation operation not legal on this view


SQL>

Merge would accept a view (created with create view ...), but a view has to be updateable; this one can't be because it contains analytic function.

What's left is a PL/SQL procedure:

SQL> begin
  2    for cur_r in (with
  3                    t1 as (select oid, label, rowid rwid,
  4                             row_number() over (partition by oid order by label) rn
  5                           from tab1
  6                          ),
  7                    t2 as (select oid, id, rowid rwid,
  8                             row_number() over (partition by oid order by id) rn
  9                           from tab2
 10                          )
 11                    select b.rwid, a.label
 12                    from t1 a join t2 b on a.oid = b.oid and a.rn = b.rn
 13                 )
 14    loop
 15      update tab2 b set
 16        b.label = cur_r.label
 17        where b.rowid = cur_r.rwid;
 18    end loop;
 19  end;
 20  /

PL/SQL procedure successfully completed.

SQL> select * from tab2 order by oid, id;

       OID         ID LAB
---------- ---------- ---
         1       2347 mx1
         1       5678 mx2
         1       9687 mx3
         2       3455 mx4
         2       4567 mx5

SQL>

Maybe someone has another idea; I'd like to see it & learn something new.