[milindoke]

is it possible in Oracle 9i to update an entire column with a collection variable. eg
in my stored prc i have a pl/sql table "mytab" which has one column "mysal". i do a bulk collect into this table for all the records from the employee table. i process the data and have made the changes to the salary in mytab. now i need to bulk update the entire column salary of employee table with mysal of mytab.
just like bulk collect, does Oracle provide for any way of doing a bulk update

[andrewst]

Originally posted by milindoke
is it possible in Oracle 9i to update an entire column with a collection variable. eg
in my stored prc i have a pl/sql table "mytab" which has one column "mysal". i do a bulk collect into this table for all the records from the employee table. i process the data and have made the changes to the salary in mytab. now i need to bulk update the entire column salary of employee table with mysal of mytab.
just like bulk collect, does Oracle provide for any way of doing a bulk update

Yes, there is FORALL:

declare

  type num_tab is table of number index by binary_integer;
  empno_tab num_tab;
  sal_tab num_tab;

begin

  select empno, sal
  bulk collect into empno_tab, sal_tab
  from emp;

  for i in 1..empno_tab.COUNT loop
    sal_tab(i) := sal_tab(i) + 10000;
  end loop;

  forall i in 1..empno_tab.COUNT
     update emp set sal = sal_tab(i)
     where empno = empno_tab(i);

end;
/

This does of course require that you saved the PK values into a table as well as the salaries!

[milindoke]

what if i skip the "WHERE" caluse of the update. actually i am in a situation where i do not have PK. so i was expecting a column overlap sortof thing. i might be sounding absurd but cant help it. to explain a bit further consider that i have the employee table sorted on emp_id and when the sal_tab is loaded i sort on emp_id. since both are sorted on emp_id, i want to actually replace the entire column salary with this sal_tab.

[andrewst]

This is not a good way to do things, but it could be done like this:

declare
  cursor c is
    select sal from emp
    order by empno
    for update of sal;

  i integer :=0;
  ...
begin
  for r in c loop
    i := i+1;
    update emp set sal = saltab(i)
    where current of c;
  end loop;
end;
/

There is no way to do that in one statement, unless you have the table of PK values.