how to set value of column to a string out of selected options?

Question

I have the following string literals

('Tv','Radio','Magazine','Internet')

I want to set the value of a certain column in a table to any of the above randomly.

so I would like to say

update table set type to (('Tv' or 'Radio' or 'Magazine' or 'Internet'))

[This question](http://stackoverflow.com/questions/1045138/how-do-i-generate-random-number-for-each-row-in-a-tsql-select) deals with random numbers in SQL server. Does it help? — nurdglaw, Jun 18 '13 at 10:00

JamieA · Accepted Answer · 2013-06-18T10:07:29.250

1

you can get a random number in range 0 to 3 using

select aBS(CHECKSUM(NewId())) % 4

This works because newid() is a non deterministic function, and will return a different (random) value for each row. So use this to seed a case statement

update table set type = case aBS(CHECKSUM(NewId())) % 4 
when 0 then 'Tv'
when 1 then 'Radio' 
when 2 then 'Magazine'
when 3 then 'Internet'  end

edited Jun 18 '13 at 10:07

answered Jun 18 '13 at 10:00

JamieA

1,984
4
27
38

is it possible to avoid the 0 option? – Farhad-Taran Jun 18 '13 at 10:11
the actual numbers are not important to the query, but if you want 1-4, then you can use (aBS(CHECKSUM(NewId())) % 4) +1 – JamieA Jun 18 '13 at 10:13

score 1 · Answer 2 · answered Jun 18 '13 at 10:00

1

CREATE TABLE #rnd (col VARCHAR(25))
INSERT INTO #rnd
SELECT slit FROM
(
   SELECT 'Tv' AS slit
   UNION ALL
   SELECT 'Radio' AS slit
   UNION ALL
   SELECT 'Magazine' AS slit
   UNION ALL
   SELECT 'Internet' AS slit
) AS foo
GO
SELECT TOP 1 * FROM #rnd ORDER BY newid()

answered Jun 18 '13 at 10:00

If your list/items change you can run a query from string (sp_executesql) after you exploded your items. So this solution is pretty flexible; can pass the list to a function/SP to give back items randomly – Jun 18 '13 at 10:06

how to set value of column to a string out of selected options?

2 Answers2