使用4个值数组中的随机文本值更新列的SQL查询

Question

我的表中有一列，我想用从4个值数组中随机选择的文本更新每一行数组={value1，value2，value3，value4}是否有一个查询从数组中选择一个随机值d=然后更新字段？

Answer 1

我通过每次使用不同的值运行下面的查询4次来绕过它，以覆盖所有4个值{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

Answer 2

我建议在一次更新中执行此操作：

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;