how to update all rows of a column in oracle with a start value of 500 and incrementing in oracle
I won't use PL/SQL since it could be done in plain SQL.
You could use a SEQUENCE starting with 500
and incremented by 1
.
For example,
set up
SQL> DROP SEQUENCE s ;
Sequence dropped.
SQL>
SQL> CREATE SEQUENCE s START WITH 500 INCREMENT BY 1;
Sequence created.
SQL>
SQL> DROP TABLE t PURGE;
Table dropped.
SQL>
SQL> CREATE TABLE t AS SELECT LEVEL id FROM dual CONNECT BY LEVEL < =20;
Table created.
SQL>
SQL> SELECT * FROM t;
ID
----------
1
2
3
4
5
6
7
8
9
10
11
ID
----------
12
13
14
15
16
17
18
19
20
20 rows selected.
SQL>
Now, let's update the table with the sequence.
SQL> UPDATE t SET ID = s.nextval;
20 rows updated.
SQL>
SQL> SELECT * FROM t;
ID
----------
500
501
502
503
504
505
506
507
508
509
510
ID
----------
511
512
513
514
515
516
517
518
519
20 rows selected.
SQL>
So, you have all the rows updated with the sequence starting with 500 and incremented by 1.
Please try like this,
DECLARE
VAL = 500;
BEGIN
FOR REC IN ( SELECT
*
FROM
Table1
)
LOOP
UPDATE Table1 SET col1 = VAL WHERE COL1 = REC.COL1 ;
VAL = VAL +1;
END LOOP;
END;