I'm handling a MySQL table here that's keyed inside a somewhat unfortunate way. Rather than utilizing an auto increment table like a key, it utilizes a column of decimals to preserve order (most probably so its fairly simple to place new rows while protecting a principal key and order).

Before Time passes through and redo this table to some thing sane, I have to learn how to rekey it having to break everything.

What I must do is one thing that can take a listing of doubles (the present secrets) and results a listing of integers (which may be cast lower to doubles for rekeying).

For instance, input gives output As this is a database, I should also have the ability to update the rows nicely:

UPDATE table SET `key`='3.00' WHERE `key`='2.50';

Can anybody think about a fast formula to get this done? My current thought would be to read all the doubles right into a vector, take how big the vector, and output a brand new vector with values from 1 => doubleVector.size. This appears pretty slow, since you wouldn't like to see every value in to the vector if, for example, just the last n/100 elements must be modified.

I believe there's most likely something I'm able to do in position, since only values following the first non-integer double have to be modified, however i can't for that existence of me figure anything out that will allow me to update in position too. For example, setting 2.60 to three.00 the very first time the thing is 2.50 within the original key list would lead to a mistake, because the key value 3.00 has already been employed for the table.


Edit: I you know what this really abstracts to is:

I want a method to convert an purchased map keyed with doubles into an purchased map keyed with integers, where at no reason does there ever exist two values for just one key (the industry breach of the map anyway).

I am presuming you'll have the ability to go ahead and take database lower sooner or later to create this conversion.

Note: I'm not a MySQL user. My DB of preference is PostgreSQL, so there Might Be SYNTAX ERRORS here between how MySQL will it and Pg will it. But this will provide you with a wise decision.

First, create a keymap table that maps old secrets to new:

create table keymap (
    oldkey decimal,
    newkey integer autoincrement
)

Make certain you index keymap because we are likely to be searches aplenty onto it.

create unique index keymap_oldkey on keymap(oldkey);

Then grow it with old secrets and let MySQL create they:

insert into keymap
    select distinct `key` from fribbles order by `key`

Now you will have keymap with the old secrets, and since you have not specified a brand new key, you will have the autoincrement around the newkey column will populate, as well as your table may be like.

oldkey    newkey
----------------
1.5       1
1.6       2
1.93      3
3.1       4

Now, give a newkey column for your tables that require it

alter table fribbles add column newkey integer

Don't allow it to be autoincrement, because otherwise it'll get populated at alter time, and that we have no need for that.

Now, finally, update the fribbles table:

update fribbles f
    set newkey = ( select newkey from keymap m where m.oldkey = f.`key` )

Finally, now that you've got newkey populated, you are able to drop that old one.

alter table fribbles drop column `key`;
alter table fribbles alter column newkey rename to `key`;

Hopefully provides you with a good strategy.

I'd just add an int column (that enables NULL values) towards the table, then perform a cursor- or code-based run where I sort through the original whacked-out double PK column after which iterate with the records writing an incremented value in to the new int column. Then update the table by altering the PK towards the new int column and removing that old PK.

"Here", as the saying goes in France.