Hello,

I have a database where a program worked nicely 390962 times, and then went nuts about 400,000 times before it was noticed.

It looks something up online and inserts the results into a table.

The first 390962 rows of the table are fine, and took about three months to build. (Which is why I don't wipe it all out and start over.) It did the next 404483 rows in about ten minutes and populated it with junk data.

The problem in the program has been corrected, but the database is still full of junk data.

Unfortunately, the table has no primary key so I can't tell it to simply delete everything where id > 390962.

If I was on Oracle, I could use rowid, but I understand MySQL doesn't have that.

Nothing has been previously deleted from the table, so I know that the whole table is still in insertion order.

I'm using MySQL 3.23.58.

Any ideas on how I can get rid of all the records past the 290962'nd one?

Thanks,
Wayne

Is there a time stamp you can use? Alternately, can you modify the table to insert an autonumber, and then use the resulting field value (not sure if you can in MySQL, but you can with other DBs...)?

Unfortunately there's no timestamp - just three fields, none of each have any particular order to them. (I'd like to get the guy who set up the table into a dark alley sometime.)

I thought about adding an id field and setting it to autonumber and primary key, but I wasn't sure if that would mess up the insertion order or not. Right now, the only thing differentiating good data from bad is that all the bad data was entered last. If I do anything that might change the row order, I'll have to scrap everything and start over from scratch.

Is it possible to add a PK to an existing table while reliably maintaining the row order?

Wayne

This post has been edited by wzeller: 4 Dec, 2006 - 08:02 AM

My suggestion would be to test this idea. In theory you should be able to add an autoincrement column without altering the row order.

Setup a test table... heh, even make a copy of this table to play with. Make note of some sample rows and their order. Take several samples. Then add the column with the PK autoincrement. See if it works.

Otherwise you may consider writing a script/app to pull in the records and start removing from the point specified.