Reassigning the values of a table PK randomly

Question

Which is the easiest way to perform the following in MySQL 5.1?

I have a table with a primary key as an integer, currently running from 1 to 220. The PK runs sequentially depending on the order in which the rows were written to the table.

I want to be able to randomly reassign this primary key value, so that, for example, row 1 (with a PK of 1 currently) becomes 19 (for example), row 2 becomes 142 (for example), row 3 becomes 99 (for example), etc. and so forth so that all numbers between 1 and 220 will be reassigned to the PK.

Is there a simple way of doing this?

Thanks, Tim

Answer 1

Update the primary key field with the Rand Function. To get an integer, you'd have to multiply it by 100, 1000, etc (depending on how big of a number you wanted) and then truncate the remaining decimal.

Your script, which I'd presume you'd write to do this, would need to ensure that a duplicate number wasn't generated and thus a failed attempt to update was made. I'd do this via a loop, an single update statement wouldn't work because of how Rand works. (At least for other RDBMS)

Is it important for us to know the reason for your doing this? The reason might change my answer...

Answer 2

There is no simple way to do it entirely within SQL. (There is most likely a complex way that isn't worth it.) I recommend you make it the responsibility of application-level logic.

I also recommend that if this is for some kind of 'card-shuffling' type purpose, you use a secondary unique key instead of the primary key.

Answer 3

You'd want to assign a random value to the ID column, then sort by it and reassign the ID based on position in the sorted rows.

Answer 4

Hi,

Thanks for your answers. However, I found a reply to a post which does exactly what I required. It is here:

http://stackoverflow.com/questions/602952/mysql-query-to-assign-a-unique-random-number-to-each-row