3 Replies Latest reply: Nov 13, 2012 5:57 PM by user489948 RSS

    How to update these two tables

    user489948
      Hello,

      I have two tables (in Oracle 11g R2), and have to lock certain rows in each one of them for update...
      Here is the sample data and expected result after updating, please help me for update statements.
      Thanks in advance!!!
      drop table t1;
      drop table t2;
      create table t1(
      t1_id     number(5) primary key,
      t1_col2   varchar2(20),
      t1_col3   varchar2(10),
      t2_id     varchar2(5));
      
      create table t2(
      t2_id    varchar2(5) primary key,
      t2_col2  varchar2(10),
      t2_col3  number(2),
      t1_id    number);
      
      insert into t1 values(1, '1 - col2', 'AB', null);
      insert into t1 values(2, '2 - col2', 'AB', null);
      insert into t1 values(3, '3 - col2', 'AB', null);
      insert into t1 values(4, '4 - col2', 'AC', null);
      insert into t1 values(5, '5 - col2', 'AC', null);
      insert into t1 values(6, '6 - col2', 'AC', null);
      insert into t1 values(7, '7 - col2', 'AC', null);
      insert into t1 values(8, '8 - col2', 'AC', null);
      insert into t1 values(9, '9 - col2', 'AC', null);
      insert into t1 values(10, '10 - col2', 'AC', null);
      commit;
      insert into t2 values('11001', 'ABC', 12, null);
      insert into t2 values('11021', 'ABC', 12, null);
      insert into t2 values('11022', 'ABC', 12, null);
      insert into t2 values('11023', 'ABC', 12, null);
      insert into t2 values('11024', 'ABC', 12, null);
      insert into t2 values('11025', 'ABC', 12, null);
      insert into t2 values('11030', 'ABC', 12, null);
      insert into t2 values('11035', 'ABC', 12, null);
      insert into t2 values('11051', 'ABC', 12, null);
      insert into t2 values('11061', 'ABC', 12, null);
      insert into t2 values('11071', 'ABC', 12, null);
      insert into t2 values('11081', 'ABC', 11, null);
      insert into t2 values('11091', 'ABC', 11, null);
      commit;
      
      
      declare
        cursor c1 is select * 
                       from t1
                      where t1_id in(select t1_id from (select t1_id from t1 where t1_col3 = 'AC' order by t1_id) where rownum <= 5)
                     for update;
      
        cursor c2 is select * 
                       from t2
                      where t2_id in(select t2_id from (select t2_id from t2 where t2_col3 = 12 order by t2_id) where rownum <= 5)
                     for update;
      begin
        for rec_c1 in c1 loop
            ???
        end loop;
      end;
      /
      
      
      The result must look like:
      
           T1_ID T1_COL2              T1_COL3    T2_ID
      ---------- -------------------- ---------- -----
               4 4 - col2             AC         11001
               5 5 - col2             AC         11021
               6 6 - col2             AC         11022
               7 7 - col2             AC         11023
               8 8 - col2             AC         11024
      
      
      T2_ID T2_COL2       T2_COL3      T1_ID
      ----- ---------- ---------- ----------
      11001 ABC                12          4
      11021 ABC                12          5          
      11022 ABC                12          6
      11023 ABC                12          7
      11024 ABC                12          8
        • 1. Re: How to update these two tables
          bencol
          Does this help:
          select t1.t1_id ,t2.t2_id
          from (select t1_id
                      ,row_number() over(order by t1_id) t1_rn
                from   t1
                where t1_col3 = 'AC'
               ) t1
          join (select t2_id
                      ,row_number() over(order by t1_id) t2_rn
                from   t2
                where t2_col3 = 12
               ) t2
            on t1.t1_rn = t2.t2_rn
          where t1.t1_rn <= 5
          • 2. Re: How to update these two tables
            AlbertoFaenza
            With the help of Bencol :-)
            DECLARE
               CURSOR c1
               IS
                  SELECT a.t1_id, b.t2_id
                    FROM t1 a CROSS JOIN t2 b
                   WHERE (a.t1_id, b.t2_id) IN (SELECT t1.t1_id, t2.t2_id
                                                  FROM    (SELECT t1_id
                                                                , ROW_NUMBER () OVER (ORDER BY t1_id) t1_rn
                                                             FROM t1
                                                            WHERE t1_col3 = 'AC') t1
                                                       JOIN
                                                          (SELECT t2_id
                                                                , ROW_NUMBER () OVER (ORDER BY t1_id) t2_rn
                                                             FROM t2
                                                            WHERE t2_col3 = 12) t2
                                                       ON t1.t1_rn = t2.t2_rn
                                                 WHERE t1.t1_rn <= 5)
                  FOR UPDATE;
            BEGIN
               FOR rec_c1 IN c1
               LOOP
                  UPDATE t1
                     SET t2_id = rec_c1.t2_id
                   WHERE t1_id = rec_c1.t1_id;
            
                  UPDATE t2
                     SET t1_id = rec_c1.t1_id
                   WHERE t2_id = rec_c1.t2_id;
               END LOOP;
            END;
            /
            Regards.
            Al
            • 3. Re: How to update these two tables
              user489948
              Thank you very much Bencol and Alberto for your help!!!