How to update Oracle column with sequence numbers

OracleI 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;
/

Share
Tags: , , ,

7 thoughts on “How to update Oracle column with sequence numbers

  1. Dawood

    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]

    WebGuru replied on June 29th, 2009:

    You need to create the sequence object first. I have enclosed the sample SQL here:
    CREATE SEQUENCE STORE_SEQ
    START WITH 1
    MAXVALUE 999999999
    MINVALUE 0
    NOCYCLE
    CACHE 20
    ORDER;

    [Reply]

  2. T-Hohs

    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]

  3. sathishkannan

    DECLARE

    Cursor sub90
    IS
    SELECT sub90 FROM cse2014 FOR UPDATE;

    BEGIN

    FOR sub90 IN sub90 LOOP
    UPDATE cse2014
    SET sub90 = store_seq.nextval
    WHERE CURRENT OF sub90;

    END LOOP;
    commit;
    END;
    /
    hi,sub90 is a column name and cse2014 table name
    i got error
    FOR sub90 IN sub90 LOOP
    *
    ERROR at line 9:
    ORA-06550: line 9, column 14:
    PLS-00364: loop index variable ‘SUB90′ use is invalid
    ORA-06550: line 9, column 1:
    PL/SQL: Statement ignored

    [Reply]

Leave a Reply

Your email address will not be published. Required fields are marked *


six − = 2

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>