Update Table

[Sheikha]

Hi:

i have a table which already has data. now i want to add a new column with data. teh column acts as a serial no. how do i update the current records with the new serial no? i did teh bellow:

DECLARE x NUMBER := 1;
BEGIN
WHILE x < 601 LOOP
UPDATE TABLE1
SET SERIAL_NO =x;
x := x+1;
END LOOP;
END;

The following error has occurred:

ORA-30036: unable to extend segment by 1024 in undo tablespace 'UNDOTBS1'

ORA-06512: at line 4

Kindly help.

[salvelinus]

I don't know what database or language that is; guessing Oracle. Anyway, think the only way you can add a column is to use SQL to ALTER TABLE first. Maybe Oracle has another way.

[Sheikha]

thanks for your promt answer.

I already added the new column. now i want to update the table with the serial numbers into this new column. how do i do that??

[salvelinus]

Are the serial numbers given, or can they be anything? If so, an autoincrement field would do the trick.
But I think what you want is to add existing serial numbers to a field in another table
The first step to the easiest solution would be to make a table with the serial numbers. If the serial numbers can refer to more than one field in your destination table (i.e. 5 could refer to widgets or widgets-lite), then you'd need another table for that.
Anyway, once you have that, you could write some code to do the updates, or one or more queries to do it.

[Sheikha]

the serail number sthat i want to add into the column are just 1,2,3,.... up to the end of the table.

i wrote this statement to do the update but it doesnt seem to work:

DECLARE x NUMBER := 1;
BEGIN
WHILE x < 500
LOOP
UPDATE APPDATA
SET CARD_NO =x;
x := x+1;
END LOOP;
END;

Kindly let me know where am i going wrong.

Thanks

[salvelinus]

Not sure because I don't know that language, but it looks like your Update would give the same number to all records 499 times. There's nothing I see that starts at one record & moves to the next.