update entire column

milindoke · #1 (**permalink**) 12-02-03, 19:07

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 · #2 (**permalink**) 12-03-03, 09:44

Quote:

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:

Code:

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 · #3 (**permalink**) 12-04-03, 15:39

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 · #4 (**permalink**) 12-04-03, 17:47

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

Code:

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech