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
Shaul behr
source
to share
3 answers
You need to add RAND
something new for each line:
RAND(CHECKSUM(NEWID()))
+3
paul
source
to share
UPDATE MyTable
SET MyCol = CAST(RAND(CHECKSUM(NEWID())) * 10 as INT) + 1
http://sqlfiddle.com/#!3/78a75/2
+2
Darren Davies
source
to share
Can you migrate the Random function using the primary key of the table?
update MyTable
set MyCol = Rand(MyIDColumn)*100
+2
bendataclear
source
to share