SQLite - Update with random unique value

If you want to later read the records in a random order, you can just do the ordering at that time:

SELECT * FROM MyTable ORDER BY random()

(This will not work if you need the same order in multiple queries.)

Otherwise, you can use a temporary table to store the random mapping between the rowids of your table and the numbers 1..N.(Those numbers are automatically generated by the rowids of the temporary table.)

CREATE TEMP TABLE MyOrder AS  SELECT rowid AS original_rowid  FROM MyTable  ORDER BY random();UPDATE MyTable  SET MyColumn = (SELECT rowid                  FROM MyOrder                  WHERE original_rowid = MyTable.rowid) - 1;DROP TABLE MyOrder;

What you seem to be seeking is not simply a set of random numbers, but rather a random permutation of the numbers 1..N. This is harder to do. If you look in Knuth (The Art of Computer Programming), or in Bentley (Programming Pearls or More Programming Pearls), one suggested way is to create an array with the values 1..N, and then for each position, swap the current value with a randomly selected other value from the array. (I'd need to dig out the books to check whether it is any arbitrary position in the array, or only with a value following it in the array.) In your context, then you apply this permutation to the rows in the table under some ordering, so row 1 under the ordering gets the value in the array at position 1 (using 1-based indexing), etc.

In the 1st Edition of Programming Pearls, Column 11 Searching, Bentley says:

Knuth's Algorithm P in Section 3.4.2 shuffles the array X[1..N].

for I := 1 to N do    Swap(X[I], X[RandInt(I,N)])

where the RandInt(n,m) function returns a random integer in the range [n..m] (inclusive). That's nothing if not succinct.

The alternative is to have your code thrashing around when there is one value left to update, waiting until the random number generator picks the one value that hasn't been used yet. As a hit and miss process, that can take a while, especially if the number of rows in total is large.

Actually translating that into SQLite is a separate exercise. How big is your table? Is there a convenient unique key on it (other than the one you're randomizing)?

Given that you have a primary key, you can easily generate an array of structures such that each primary key is allocated a number in the range 1..N. You then use Algorithm P to permute the numbers. Then you can update the table from the primary keys with the appropriate randomized number. You might be able to do it all with a second (temporary) table in SQL, especially if SQLite supports UPDATE statements with a join between two tables. But it is probably nearly as simple to use the array to drive singleton updates. You'd probably not want a unique constraint on the random number column while this update is in progress.