SQL Server: Update a colum with Random Value

Question

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?

Why do you have the values of genre stored in the Product table? You should have a Genre table and a foreign key in the Product table. The issue with your question is that it is not at all clear what you are trying to do. Are you wanting to randomly pick one of those values for every row in your product table? — Sean Lange, Aug 06 '15 at 13:49
possible duplicate of [How can I insert random values into a SQL Server table?](http://stackoverflow.com/questions/1468159/how-can-i-insert-random-values-into-a-sql-server-table) — Bulat, Aug 06 '15 at 13:53
The values that I have mentioned above need to be randomly updated in the Genre column of the Product table. — Tauseef Hussain, Aug 06 '15 at 13:53
You would still be far better off if you normalized the Genre to a lookup table instead of repeating the values over and over. — Sean Lange, Aug 06 '15 at 13:59

score 2 · Answer 1 · answered Aug 06 '15 at 13:53

2

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());

answered Aug 06 '15 at 13:53

Gordon Linoff

1,242,037
58
646
786

Never used with before. It say `Lookup Error - SQL Server Database Error: Incorrect syntax near ')'.` (The bracket that closes the with statement) – Tauseef Hussain Aug 06 '15 at 14:06
@TauseefHussain . . . Did you replace the `. . .` with the additional values that you want? – Gordon Linoff Aug 06 '15 at 23:03

score 2 · Accepted Answer · answered Aug 06 '15 at 14:00

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

SQL Server: Update a colum with Random Value

2 Answers2