Assign a "record number" to each row through an UPDATE clause

SQL Apprentice Question
I've a table with an interger column that accept NULL values.
When records are inserted, this column is not specified.
I would like to assign a "record number" to each row through this column
starting with 1 to each record with a single UPDATE command.
For instance, if the table contains 10 rows, atfer the UPDATE command, each
row will have a value in the interger column starting from 1 up to 10.
Is it possible ?

The primary key is a string...

sample data before UPDATE:


primary_key datetime imagedata record_number
olivier some_dt some_data NULL
olivier some_dt some_data NULL


After UPDATE


primary_key datetime imagedata record_number
olivier some_dt some_data 1
olivier some_dt some_data 2





Celko Answers
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

The personal pseudo-code you did post, with all of the illegal columns
names, is useless. Your "primary key" column has duplicate values. My
guess is that you meant to say:


CREATE TABLE Foobar
(vague_name VARCHAR(35) NOT NULL,
vague_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (vague_name, vague_date),
etc.);


Your attempt to mimic a 1950's magnetic tape file in SQL is a bad idea.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. If you want an ordering, then you need to have a column
that defines that ordering. It is an attribute of the entity in the
data model, not a physical locator put on as an afrter thought.


In your case, you probably should use a VIEW with a "ROW_NUMBER () OVER
(PARTITION BY vague_name ORDER BY vague_date DESC)" function in
SQL-2005 or a self-join. PARTITION gives you a "row number" in each
vague_name grouping; drop it and you will get the whole table in
temporal order.