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