Fixing up field with a row number to become PK

[ruthc]

Folks,

I have a database that for historical reasons has a table with a
field, LineID, which currently always contains 0. This field ought to
be the primary key of the table, but the value ought to come from
another table that is an AutoNumber. I will fix the system to make
both true, but for now I need to fix the data.

My task therefore is to fill in the LineID records with a unique
number so that I can mark it as a primary key. There is a max value of
such numbers. The simplest option seemed to be something like UPDATE
table SET LineID = ROW_NUMBER(), but access doesn't do that.

The other fields of the table aren't sufficient to make a primary key,
so I can't use solutions that require an "order by"; there's nothing
to order on.

At the moment the top runner seems to be move the table to SQL Server
and run the SQL above there.

Any other options?

 

[John W. Vinson]

Folks,

I have a database that for historical reasons has a table with a
field, LineID, which currently always contains 0. This field ought to
be the primary key of the table, but the value ought to come from
another table that is an AutoNumber. I will fix the system to make
both true, but for now I need to fix the data.

My task therefore is to fill in the LineID records with a unique
number so that I can mark it as a primary key. There is a max value of
such numbers. The simplest option seemed to be something like UPDATE
table SET LineID = ROW_NUMBER(), but access doesn't do that.

The other fields of the table aren't sufficient to make a primary key,
so I can't use solutions that require an "order by"; there's nothing
to order on.

At the moment the top runner seems to be move the table to SQL Server
and run the SQL above there.

Any other options?

I'd suggest creating a new empty table with the same fields as this one (copy
and paste the table, DESIGN VIEW ONLY); add an Autonumber field. Then run an
Append query to migrate the data into this one.

You can then run an update query updating LineID to the value of the
autonumber field, and then delete the autonumber field.

The numbers will be arbitrary of course, since you have no defined order to
the records. How you'll synchronize the LineID fields between these two tables
is a mystery to me, since based on what you've posted there is no connection.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com