How to populate a database column with random numbers

Question

How do I populate a int column, currently empty, with random numbers with no duplicates?

Answer 1

I suppose you could make the column a primary key to prevent duplicates, though that's kind of a hack. You can remove the key later.

---- Create the variables for the random number generation
DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT;
DECLARE @Index integer

---- This will create a random number between 1 and 999
SET @Lower = 1 ---- The lowest random number
SET @Upper = 999 ---- The highest random number
SET @Index = 0 --- A while loop counter

--- Loop from 0 to 10
WHILE @Index < 10
BEGIN
  SELECT 'loop counter = ', @index
  SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
  --Insert @Random here.
  SET @index = @index + 1
END

Answer 2

If this is an existing table to which you added a new INT column, you can do something like this:

UPDATE MyTable
SET MyIntColumn = CONVERT(int, RAND(CHECKSUM(NEWID())) * 10000);

This will populate the empty column with random numbers between 1 and 10000.

Answer 3

What is the mysql stored procedure for this and how can you insert the values into a column in a table?

I've tried one update statement, like Jose's answer but all I get is the following:

mysql> describe t1;

+-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | i1 | int(11) | NO | PRI | NULL | | | i2 | int(11) | NO | | NULL | | | i3 | int(11) | NO | | NULL | | +-------+---------+------+-----+---------+-------+ 3 rows in set (0.00 sec)

mysql> update t1 set t1.i2= CONVERT(INT, RAND(CHECKSUM(t1.i2)) * 1000);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INT, RAND(CHECKSUM(t1.i2)) * 1000)' at line 1