CODEWITHSUNDEEP
oracle-databaseplsql
Robert Pinto
Robert Pinto

Reputation: 1

Update Column using pl/sql

I have a table T1 which 3 columns with 100 records. All columns and rows were filled but the first column named ID values are empty. So, I wanted to fill the ID column with numbering order(1,2..100) for 100 rows by using PL/SQL Program. I have tried with rownum and with a sequence which is working fine. I want to try with pl/SQL block. I have also tried to write pl/SQL block, however, not getting the desired result.

declare
count1 number;
begin
SELECT COUNT(1) INTO COUNT1 FROM T1;
FOR I IN 1..COUNT1
loop
UPDATE T1 SET ID =I;
end loop;
end;

Upvotes: 0

Views: 848

Answers (1)

Littlefoot
Littlefoot

Reputation: 142710

SQL should be the way to do it; but OK, if you're learning PL/SQL, then this might be one option:

Sample table (ID column should be populated):

SQL> create table test (id number, name varchar2(10));

Table created.

SQL> insert into test (name) select ename from emp;

14 rows created.

SQL> select * From test;

        ID NAME
---------- ----------
           SMITH
           ALLEN
           WARD
           JONES
           MARTIN
           BLAKE
           CLARK
           SCOTT
           KING
           TURNER
           ADAMS
           JAMES
           FORD
           MILLER

14 rows selected.

Anonymous PL/SQL block:

SQL> declare
  2    i number := 1;
  3  begin
  4    for cur_r in (select rowid rid from test) loop
  5      update test set id = i where rowid = cur_r.rid;
  6      i := i + 1;
  7    end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

Result:

SQL> select * From test;

        ID NAME
---------- ----------
         1 SMITH
         2 ALLEN
         3 WARD
         4 JONES
         5 MARTIN
         6 BLAKE
         7 CLARK
         8 SCOTT
         9 KING
        10 TURNER
        11 ADAMS
        12 JAMES
        13 FORD
        14 MILLER

14 rows selected.

SQL>

Loops are slow, they process the table row-by-row. Yet another option (you didn't mention and - perhaps - didn't try - is merge.

SQL> update test set id = null;

14 rows updated.

SQL> begin
  2    merge into test a
  3      using (select b.rowid,
  4                    row_number() over (order by null) rn
  5             from test b
  6            ) x
  7      on (a.rowid = x.rowid)
  8      when matched then update set
  9        a.id = x.rn;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> select * from test;

        ID NAME
---------- ----------
         1 SMITH
         2 ALLEN
         3 WARD
         4 JONES
         5 MARTIN
         6 BLAKE
         7 CLARK
         8 SCOTT
         9 KING
        10 TURNER
        11 ADAMS
        12 JAMES
        13 FORD
        14 MILLER

14 rows selected.

SQL>

Upvotes: 2

Related Questions