I have a Oracle database table with some fields (columns) in numeric format. Now I need to update one column, namely store_id, to make it a primary column with unique number id. Since there were a lot of duplicate data in this column, the best way to do is to update this column with the sequence numbers.
To accomplish this, I need to create a sequence object in Oracle, and use Oracle cursor to loop and update the table column.
Here is the PL/SQL I used and worked.
Tags: Database, Oracle, PL/SQL, SQL
SELECT store_id FROM store_locations FOR UPDATE;
FOR c_store_id IN store_id LOOP
SET store_id = store_seq.nextval
WHERE CURRENT OF store_id;