Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Location
    Atlanta
    Posts
    8

    Unanswered: update entire column

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: update entire column

    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!

  3. #3
    Join Date
    Apr 2003
    Location
    Atlanta
    Posts
    8
    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.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •