CODEWITHSUNDEEP
sqlsql-server
hanan shater
hanan shater

Reputation: 27

SQL query to update a column with a random text value from an array of 4 values

I have a column in my table that I want to update each row with text selected randomly from an array of 4 values array = {value1, value2, value3, value4} is there a query to select one random value d=from the array then update the field ?

Upvotes: 0

Views: 1334

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

I would suggest doing this in a single update:

with vals as (
      select v.val, count(*) over () as num_vals,
             row_number() over (order by val) - 1 as seqnum
      from (values ('val1'), ('val2'), ('val3'), ('val4')) v(val)
     )
update t
    set t.d = v.val
    from (select t.*,
                 row_number() over (order by newid()) - 1 as seqnum
          from t
         ) t join
         vals
         on vals.seqnum = t.seqnum % vals.cnt;

Upvotes: 1

hanan shater
hanan shater

Reputation: 27

I got around it by running below query 4 times each time with a different value to cover all 4 values {value1, value2, value3, value4}

   DECLARE @Counter INT , @Rand INT 
SET @Counter=1
SET @Rand= FLOOR(RAND()*(1476-1+1))+1
WHILE ( @Counter <= 400)
BEGIN
    update Table set ConfidentialityLevel = 'Value1'   where ID = @Rand
    print @Rand
    SET @Rand= FLOOR(RAND()*(1476-1+1))+1
    SET @Counter  = @Counter  + 1
END

Upvotes: 0

Related Questions