conditional UPDATE in ORACLE with subquery

Question

I have a table ( table1 ) that represents item grouping and another table ( table2 ) that represents the items themselves.
table1.id is foreign key to table2 and in every record of table1 I also collect information like the total number of records in table2 associated with that particular record and the sum of various fields so that I can show the grouping and a summary of what's in it without having to query table2.
Usually items in table2 are added/removed one at a time, so I update table1 to reflect the changes in table2.

A new requirement arose, choosen items in a group must be moved to a new group. I thought of it as a 3 step operation:

create a new group in table1
update choosen records in table2 to point to the newly created rec in table1

the third step would be to subtract to the group the number of records / the sum of those other fields I need do show and add them to the new group, data that I can find simply querying table2 for items associated with the new group.

I came up with the following statement that works.

update table1 t1 set 
countitems = ( 
    case t1.id 
      when 1 then t1.countitems - ( select count( t2.id ) from table2 t2 where t2.id = 2 )
      when 2 then ( select count( t2.id ) from table2 t2 where t2.id = 2 )
    end
), 
sumitems = (
    case t1.id
      when 1 then t1.sumitems - ( select sum( t2.num ) from table2 t2 where t2.id = 2 )
      when 2 then ( select sum( t2.num ) from table2 t2 where t2.id = 2 )
    end
)
where t1.id in( 1, 2 );

is there a way to rewrite the statement without having to repeat the subquery every time?

thanks
Piero

score 0 · Answer 1 · answered Sep 18 '12 at 11:32

You can use a cursor and a bulk collect update statement on the rowid. That way you can simply write the join query with the desired result and update the table with those values. I always use this function and make slight adjustments each time.

declare
    cursor cur_cur
    IS
    select ti.rowid     row_id
    ,      count(t2.id) countitems
    ,      sum(t2.num)  numitems
    from   table t1
    join   table t2 on t1.id = t2.t1_id
    order by row_id
    ;

    type type_rowid_array is table of rowid index by binary_integer;
    type type_countitems_array is table of table1.countitems%type;
    type type_numitems_array is table of table1.numitems%type;

    arr_rowid type_rowid_array;
    arr_countitems type_countitems_array;
    arr_numitems type_numitems_array;

    v_commit_size number := 10000;

begin
    open cur_cur;

    loop
        fetch cur_cur bulk collect into arr_rowid, arr_countitems, arr_numitems limit v_commit_size;

        forall i in arr_rowid.first .. arr_rowid.last
            update table1 tab
            SET    tab.countitems = arr_countitems(i)
            ,      tab.numitems = arr_numitems(i)
            where  tab.rowid = arr_rowid(i)
            ;

        commit;
        exit when cur_cur%notfound;

    end loop;

    close cur_cur;
    commit;

exception
  when others
    then rollback;
         raise_application_error(-20000, 'ERROR updating table1(countitems,numitems) - '||sqlerrm);

end;

conditional UPDATE in ORACLE with subquery

1 Answers1