Clever Geek Handbook

SQL Server: updating a column with a random value

I have a table Product

and I have a named column Genre

that has null / unwanted values.

I want to update this 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 to be updated. How should I do it?

+3


source to share


2 answers


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
+2


source


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 is optimizing the subquery to run only once. If so, then the correlation condition 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());
+2


source






More articles:


All Articles