How to update table column with random value from a list of values?

Question

I am trying to update a table with random values from a specific list of values. I got as far as the code below which does not work because the same value is being inserted into each row:

UPDATE [Post]
SET UserID = (
    /** If I run this select statement independently then I get random value each time but not in this update statement **/
    SELECT TOP (1) UserID 
    FROM
    [User]
    WHERE UserID IN (1,3,4,7)
    ORDER BY NEWID()
)
WHERE
    UserID <> 10 AND UserID <> 11

I tried looking at using ABS(CHECKSUM(NEWID()))%4 + 5 but generates any number between 1 and 4 which is not what I want. It has to be one of the values of 1,3,4,7 or any other list of specific values.

Answer 1

Demo on db<>fiddle

You can use CROSS APPLY combine with NEWID() to get random value each row being updated

DECLARE @TempTable Table(Id int)
INSERT INTO @TempTable
VALUES(90), (80), (70)

UPDATE t1
SET Id = t2.Id
FROM @TempTable t1
CROSS APPLY (SELECT TOP 1 Id FROM(VALUES(1), (3), (4), (7))t(Id) WHERE t1.Id = t1.Id ORDER BY NEWID()) t2

SELECT * FROM @TempTable