Home » SQL & PL/SQL » SQL & PL/SQL » automatically populate UID field
automatically populate UID field [message #201786] Mon, 06 November 2006 13:43 Go to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
I added a unique identifier field to my table. I will use a sequence to increment for every new record. The problem is that there are currently 1000 records already in there. How do I assign an ID to each of these records starting at 1?

Thanks.
Re: automatically populate UID field [message #201787 is a reply to message #201786] Mon, 06 November 2006 13:45 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
update mytable set new_column=rownum;

[Updated on: Mon, 06 November 2006 13:46]

Report message to a moderator

Re: automatically populate UID field [message #201788 is a reply to message #201787] Mon, 06 November 2006 13:51 Go to previous messageGo to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
Thanks. I keep forgeting about that rownum "field" Smile

[Updated on: Mon, 06 November 2006 13:51]

Report message to a moderator

Re: automatically populate UID field [message #201796 is a reply to message #201786] Mon, 06 November 2006 15:46 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Do not use rownum, if you have a sequence defined then use it. This will set the correct value in the sequence for the next row to be inserted..

update my_table set new_column = my_sequence.nextval;
Re: automatically populate UID field [message #201930 is a reply to message #201796] Tue, 07 November 2006 07:48 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Bill B wrote on Mon, 06 November 2006 16:46
Do not use rownum, if you have a sequence defined then use it. This will set the correct value in the sequence for the next row to be inserted..

update my_table set new_column = my_sequence.nextval;


I think Mahesh was just describing how to update the 1000 rows that were already in the table.
Re: automatically populate UID field [message #201931 is a reply to message #201786] Tue, 07 November 2006 07:51 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
I understood what he was saying. What I am saying is to use the sequence to update the existing record. It will then be set to the correct value for the next new row. If you just update using rownum, then you will have to get the maximum value of the column and then adjust the sequence to be that value plus one. If you just use the sequence in the first place, no extra actions are necessary.
Re: automatically populate UID field [message #201950 is a reply to message #201931] Tue, 07 November 2006 09:31 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
True. Bill's suggestion is elegant than mine. Smile
Previous Topic: ref cursor to return boolean value
Next Topic: surrogate key
Goto Forum:
  


Current Time: Thu Dec 08 08:13:53 CST 2016

Total time taken to generate the page: 0.07236 seconds