Menu
I have a table in oracle of which all rows of a column has to be updated with a starting value of 500 and incrementing by 1.
I tried to find something similar online but wasn't able to get anything useful. oracle and PL/SQL is not my expertise. any help would be appreciated.
754
Introduction to the Oracle UPDATE statement If you update more than two columns, you separate each expression column = value by a comma. The value1 , value2 , or value3 can be literals or a subquery that returns a single value. Note that the UPDATE statement allows you to update as many columns as you want.
You cannot have more than 1000 literals in an IN clause. You can, however, have SELECT statements in your IN clause which can return an unlimited number of elements i.e. You might try using 'between' clause replacing 'in'... check documentation for correct syntax on using between.
Syntax: UPDATE table_name SET column_name1 = new_value1, column_name2 = new_value2 ---- WHERE condition; Here table_name is the name of the table, column_name is the column whose value you want to update, new_value is the updated value, WHERE is used to filter for specific data. Let's look at an example.
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.
104
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
