use sequence to update column in cursor for loop

[leonard905]

Hi,
I'm using a cursor for loop to update a column on a table with a sequence. This update should occur for all rows on the table. I get the error message :(sequence not allowed here). Is there a better way update the column with the sequence value? Please see code below:

/* This will update the sales_person_id of the salesperson table with a unique number from the sequence */

declare

cursor sales_id is
select sales_person_id from om_sales_person for update;

BEGIN

for c_sales_id in sales_id loop
UPDATE om_sales_person
SET sales_person_id = (select om_sales_person_id_seq.nextval from dual);
WHERE CURRENT OF sales_id;

END LOOP;
commit;
END;
/

[jmodic]

Just change your update statement inside the loop:


UPDATE om_sales_person
SET sales_person_id = om_sales_person_id_seq.nextval
WHERE CURRENT OF sales_id;

[leonard905]

Thanks. It worked.