Hi,

I have an existing table that I wanted to add an autoinc field to.

I added this, and it pre-populated existing records, however I have discovered that there are duplicates of the same number in the table. (In adding the new field I omitted to create a unique index on it).

If I create a unique index now - it will truncate all duplicate records, so I need to fix these up first. I was wondering if there might be a simple solution as to how to fix this? Running a repair fixes the autoinc for future records, but doesn't fix existing records.

Thanks & Regards

Adam.

Adam


Depends n what you mean by simple.

Copy the table, empty it, copy the records from the old table (SQL, navigational, export/import) without the autoinc, drop the old table, rename new one.

Delete the autoinc column and add another one (might work, I don't know)

I seem to recall that in DBISAM you can overwrite autoincs so

1. choose a large base number (say 1,000,000)
2. add an index to teh table to get the order you want
3, iterate through the table altering the autoinc to base number + counter
4. iterate through the table (or use sql) subtracting the base number from the autoinc
5. reset the seed

Job done (and memory says I've used this approach myself)



Roy Lambert

Thanks Roy!