SQL Server 2012 Random string from a list

say I have 3 values, Bill, Steve, Jack. and I want to randomly update a table with those values, e.g.

Update contacts 
set firstname = ('Bill','Steve','Jack') 
where city = 'NY'

how do I randomize these values?



  • You can do this with the following trick:

    update c set name=ca.name
    from contacts c
    outer apply(select top 1 name 
                from (values('bill'),('steve'),('jack')) n(name)
                where c.id = c.id order by newid())ca;

    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/22