sql - 使用随机名称更新 SQL Server 表

Question

我正在尝试以下操作:

update [Employees] set Last_User = 

(select top 1 name from 
(select 'John' as name 
union select 'Tim' as name
union select 'Jane' as name
union select 'Jack' as name
union select 'Steve' as name
union select 'Ann' as name
) 

as names order by newid())

但继续为所有行获取相同的名称。我怎样才能让它有所不同？

注意:名称是在查询中输入的，而不是来自另一个表。

谢谢

Answer 1

您正在尝试更新整列而不是更新每一行，因此生成的第一个值会更新到所有行，您可以使用 T-SQL 完成预期任务

DECLARE @counter int = 1

WHILE @counter <= (SELECT COUNT(1) FROM [Employees]) --or any speific row set you want to modify
BEGIN

UPDATE a
 set Last_User = 
(SELECT top 1 name from 
(SELECT 'John' as name 
UNION SELECT 'Tim' AS name
UNION SELECT 'Jane' AS name
UNION SELECT 'Jack' AS name
UNION SELECT 'Steve' AS name
UNION SELECT 'Ann' AS name
) 

AS names ORDER BY NEWID())
 FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY Last_User)rnum FROM [Employees])a
WHERE a.rnum = @counter

SET @counter = @counter + 1

END