update the id number so there is no duplicates (1 Viewer)

hllary

Registered User.
Local time
Yesterday, 19:18
Joined
Sep 23, 2019
Messages
80
I have a table that has duplicate numbers in the ID column and I want to get rid of them.

When I first populate the table I need to have duplicate numbers since I run some code that breaks part of the records into two. After I'm done with that I need to change the ID field so they are all different.

Is there a simple way to do this? Or should I create another column that uses autonumber then delete the first column? I would like it if I could run an update query that would change the ID numbers, so it can be automated.
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Yesterday, 21:18
Joined
Feb 28, 2001
Messages
18,379
Given that you can have duplicates and plan to change the ID fields, those fields can AT BEST be indexed non-primary keys that cannot participate in formal relationships. (Because the "one" side of a one/many relationship must be unique.)

Once a field has been populated, it becomes incredibly hard - maybe not possible - to change it to another type of key such as autonumber. Therefore, some kind of autonumber as a separate field might be better as a permanent prime key. If the ID field is not a prime key, then you can treat that field as you need to because at worst you force the index to be rewritten when you edit it.

Can't advise very well about the UPDATE query because your requirements are a bit vague. All I can say in general is that such queries could be written and would be of the UPDATE variety.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:18
Joined
Oct 29, 2018
Messages
13,261
Hi. If you try to change an existing numeric field in an already populated table, you will get this error message.
1603387016623.png

Instead, you will have to add a new field and make it Autonumber.
 

jdraw

Super Moderator
Staff member
Local time
Yesterday, 22:18
Joined
Jan 23, 2006
Messages
13,402
Perhaps, if readers had more info on your application and understood the requirement in context, then there may be options for consideration.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:18
Joined
Feb 19, 2002
Messages
30,148
Why not import the data to a table that has an autonumber as the PK? You can leave the old ID as a reference if you want. That way you will be able to continue to see what records were once one if that is relevant. If it isn't relevant, don't import the old ID at all. As your process splits the records, new rows will get new autonumber IDs.
 

Users who are viewing this thread

Top Bottom