Clever Geek Handbook

How do I bulk update a SQL table with random data in one step?

I have a large table where, for testing purposes, I want to update a column to a different random value in each row.

If I say:

update MyTable
set MyCol = Rand()*100

then the value is indeed random - but the same random number is applied to every line.

I could use a cursor to iterate and update each row, one by one, and this is obviously what I will return if necessary. But is there some SQL syntax that will update each row with a different random value in one condensed expression?

+3


source to share


3 answers


You need to add RAND

something new for each line:



RAND(CHECKSUM(NEWID()))

+3


source 


UPDATE MyTable
SET MyCol = CAST(RAND(CHECKSUM(NEWID())) * 10 as INT) + 1


http://sqlfiddle.com/#!3/78a75/2

+2


source


Can you migrate the Random function using the primary key of the table?

update MyTable
set MyCol = Rand(MyIDColumn)*100
+2


source






More articles:


All Articles