CODEWITHSUNDEEP
sqlsql-serverrandom
Tauseef Hussain
Tauseef Hussain

Reputation: 1079

SQL Server: Update a colum with Random Value

I have a table Product and I have a column in it called Genre which has null/unwanted values.

I want to update that column with a set of values: 

Documentary
Comedy
Adventure/Action
Drama
Thriller
SF/Fantasy
Animation/Family
Others

The update can be in any order but I want every row in the column updated. How should I go about this?

Upvotes: 3

Views: 107

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93694

Try something like this

UPDATE P
SET    genre = rand_values
FROM   Product p
       CROSS apply (SELECT TOP 1 rand_values
                    FROM   (VALUES ('Documentary'),
                                   ('Comedy'),
                                   ('Adventure/Action'),
                                   ('Drama'),
                                   ('Thriller'),
                                   ('SF/Fantasy'),
                                   ('Animation/Family'),
                                   ('Others')) tc (rand_values)
                    WHERE  p.productid = p.productid -- This is just to correlate the query 
                    ORDER  BY Newid()) cs

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269633

I think the following will work:

with genres as (
      select 'Documentary' as genre union all
      select 'Comedy' union all
      . . .
     )
update product
    set genre = (select top 1 g.genre from genres g order by newid());

It is possible that SQL Server will optimize the subquery to run only once. If that is the case, than a correlation clause should fix the problem:

with genres as (
      select 'Documentary' as genre union all
      select 'Comedy' union all
      . . .
     )
update product
    set genre = (select top 1 g.genre from genres g where g.genre <> product.genre or product.genre is null order by newid());

Upvotes: 2

Related Questions