Team,
For our discussion , let us take our scott.emp table.(Let us assume that our emp table has duplicate values for empno (No primary key column for now).)
For this table I need to add a column by name seq_num which has to have unique values starting from 1001 .
For the unique number we can use a sequence by name seq_emp
I tried with the below code , but the problem is , my code does not work when there are duplicate values in empno.
Note : There is a chance that all the records may not be unique.
My code :
Seq_num column addition script and seq_emp sequence creation script.
alter table emp add column seq_num number;
CREATE SEQUENCE seq_emp MINVALUE 1 MAXVALUE 999999999999 INCREMENT BY 1 START WITH 1001 NOCACHE NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
Plsql code block:
DECLARE
v_seq_num NUMBER := o;
BEGIN
FOR rec IN (SELECT *
FROM emp
ORDER BY empno)
LOOP
SELECT seq_emp.NEXTVAL
INTO v_seq_num
FROM DUAL;
UPDATE emp
SET seq_num = v_seq_num
WHERE empno = rec.empno AND ename = rec.ename;
END LOOP;
COMMIT;
END;
Any Help most Appreciated !!
Regards,
Satyam Reddy