Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: Automatically assigning incrementing number in SQL
There is no autonumber datatype in Oracle, but you can use a sequence object to return unique, incremental values.
From your question I assume that your table is already populated and you want to set an existing field to this incremental value.
First, create the sequence:
CREATE SEQUENCE MY_SEQ START WITH 1 INCREMENT BY 1; Now update your data:
UPDATE MY_TABLE
SET MY_COLUMN = MY_SEQ.NEXTVAL;
This will set the MY_COLUMN column for all rows to the incremental
value, starting with 1. Note that this sets the value according to the
natural order of rows as they were inserted into the table. If you
need to set them in some other specific order, you'll need to use an
inner select query with an ORDER BY clause to put the rows in the right
order.
HTH,
Dave Pulaski
Database Consultant
In article <t88a0adoo8mede_at_corp.supernews.com>,
"Sean M. Severson" <sseverson_at_2tacweb.com> wrote:
> Hello,
>
> Is there a type of sql query I can run that will assign numeric values
to
> all records in a table (a specific field) that starts at 1 and
increments up
> by 1 until the end of the table is reached? Is there an autonumber
type of
> field that will do this?
>
> --
> Sincerely,
>
> Sean M. Severson
> Project Manager
>
> ===============================
> "Our aim is to become partners in your
> success through our performance."
>
> Technical Assistance Company
> 807 Forest Avenue
> Sheboygan Falls, WI 53085
>
> (920) 467-6711
> www.2tacweb.com
> ===============================
>
>
Sent via Deja.com
http://www.deja.com/
Received on Fri Feb 09 2001 - 14:03:56 CST