Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Update a column with random value 2
- Thread starter yorge
- Start date
-
2
- #2
Olaf Doschke
Programmer
Since 'X' = CHAR(88) and 'Y' = CHAR(89) and more general all data is numbers in some binary format, a function creating random numbers will help, won't it?
There is a function for random numbers called RAND(). It results in a float value between 0 and 1 and you could project all values <.5 to 0 and >.5 to 1 (which ROUND does, for example) and add it to 88, then take CHAR() of that:
UPDATE mytable SET mycol = CHAR(88+ROUND(RAND(),1))
The only problem is T-SQL optimizes this and only computes one random value.
Fortuntely NEWID() computes a random value per row, just not numeric, but you can apply CHECKSUM() and then modulo % operation:
Bye, Olaf.
There is a function for random numbers called RAND(). It results in a float value between 0 and 1 and you could project all values <.5 to 0 and >.5 to 1 (which ROUND does, for example) and add it to 88, then take CHAR() of that:
UPDATE mytable SET mycol = CHAR(88+ROUND(RAND(),1))
The only problem is T-SQL optimizes this and only computes one random value.
Fortuntely NEWID() computes a random value per row, just not numeric, but you can apply CHECKSUM() and then modulo % operation:
Code:
UPDATE mytable SET mycol = CHAR(88+ABS(CHECKSUM(NEWID())%2))Bye, Olaf.
- Status
Similar threads
