say I have 3 values, Bill, Steve, Jack. and I want to randomly update a table with those values, eg
Update contacts set firstname = ('Bill','Steve','Jack') where city = 'NY'
how do I randomize these values?
Thanks
say I have 3 values, Bill, Steve, Jack. and I want to randomly update a table with those values, eg
Update contacts set firstname = ('Bill','Steve','Jack') where city = 'NY'
how do I randomize these values?
Thanks
7条答案
按热度按时间zmeyuzjn1#
You can do this with the following trick:
c.id = c.id
is just a dummy predicate that forces sql engine to call subquery for each outer row. Here is the fiddle http://sqlfiddle.com/#!6/8ecca/22ztyzrc3y2#
Here's some love using
choose
xj3cbfub3#
You can do something like this
The
FLOOR(RAND()*(4-1)+1)
would generate a random number from 1 to 3 everytime you run the query. Therefore, you will be picking a random name every time.mrfwxfqh4#
This is addition answer using Ben Thul answer :
Using RAND function will random values from 1 to 3. Then, based on resulted int value the CHOOSE function will pick values depending on the order/index of the given strings. In which 'Bill' is the index 1 then so on.
You can test the random int values by using below SQL script:
I have a weird issue if use the RAND directly to the query for example below:
There are instance that value is NULL.
vhmi4jdf5#
This might answer your question:
How do I generate random number for each row in a TSQL Select?
Use RAND to generate a number that determines which one to use.
8ehkhllq6#
Tried all suggestions above, ended up indexing to ID to generate a random index on a CHOOSE function
where "3" is the number of items of your list. It's not really random as it is indexed to your row id, but it's random enough so each row is different.
ukxgm1gy7#