Updating a SQL Server table with random names

Question

I am trying the following:

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

but keep getting the same name for all rows. how can I make it vary?

Note: names are entered in query and do not come from another table.

Thanks

Answer 1

You are trying to update the whole column instead of updating each row, hence the first value which is getting generated is updated to all the rows, you can do the intended task by using 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