Friday, September 12, 2014

adding a primary/unique key to existing table and updating values

lets take a simple example here. create a table and leave id column empty and add values to other column name.

Sample create table :

create table abc (id number, name varchar2(20));


add values to name column and leave the id column empty

INSERT INTO ABC (NAME) VALUES ('a');
INSERT INTO ABC (NAME) VALUES ('b');
INSERT INTO ABC (NAME) VALUES ('c');
INSERT INTO ABC (NAME) VALUES ('d');
INSERT INTO ABC (NAME) VALUES ('e');
INSERT INTO ABC (NAME) VALUES ('f');
INSERT INTO ABC (NAME) VALUES ('g');
INSERT INTO ABC (NAME) VALUES ('h');


Now we have a situation where we have a table with only values in the name column and id column is empty (in case if u want to add id's to already existing table  just add a column for the new unique ID's ). 

Now we will create a sequence to get the sequential values to update with.

create a sequence :

CREATE SEQUENCE SEQ_abc
START WITH 1
MAXVALUE 99999
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;

This cursor will update the ID's columns with the new unique ID's from above sequence sequentially. execute below block and all set.

Update cursor:

DECLARE

Cursor store_id
IS
SELECT id FROM abc FOR UPDATE;

BEGIN

FOR c_store_id IN store_id LOOP
UPDATE abc
SET id = SEQ_abc.nextval
WHERE CURRENT OF store_id;

END LOOP;
commit;
END;

/

Note: This is a one time update, use a trigger  to keep column updated everytime.

No comments:

Post a Comment