Hi all,
I hate asking SQL specific questions here, but if I could get a response privately it would be great. I have asked a few places and it's tough finding a good answer. What I'm looking for is a way to shuffle IDs in a database via SQL that doesn't require me to select the entire DB out, renumber it, and insert it entirely back in (since the database can have over 13M+ rows, this can take literally forever).
Basically I have 3 rows. An integer primary key, a text string and an id integer. What I want to do is shuffle the id integer and change it's value to a unique number, in essence just shuffling the value for that column, and all rows have to have a unique integer number, but they must all change when I do this shuffle and be unique. The problem is I can't think of any way of doing this via SQL. What I'm aiming for is something that takes less than say an hour to do this, even faster would be a bonus.
Example:
1 'hello' 5 2 'good morning' 6 3 'good night' 7 4 'good bye' 8
after shuffling would become something like:
1 'hello' 7 2 'good morning' 6 3 'good night' 8 4 'good bye' 5
but for millions of rows.
Another note: I select out of the database in order and limited. That is, when selecting out I use that ID row to select out say 5000 at a time. So the id field doesn't have to have the same ranges as it did before, in fact they can be completely randomized to any number, but they all have to be unique.
Thanks in advance,
Jay Macaulay
