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.
DECLARE
Cursor store_id
IS
SELECT store_id FROM store_locations FOR UPDATE;BEGIN
FOR c_store_id IN store_id LOOP
UPDATE cms_store_locations
SET store_id = store_seq.nextval
WHERE CURRENT OF store_id;END LOOP;
commit;
END;
/
Greetings,
When I tried to run this PL/SQL the oracle generate an error which is that the sequence store_seq doesn`t exist..
So, if you have some explanation or solution gor this problem plz send me it through my email.
regards..
[Reply]
Hey great post!
Couldn’t you simplify even further by doing the following and eliminating the PL/SQL:
UPDATE cms_store_locations
SET store_id = store_seq.nextval;
[Reply]