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.

this code won't work because all rows of post table will be updated with whatever random number is returned by inner select. e.g. if inner select gives 3 then all filtered rows on Post table will be updated with 3. — Amit Kumar, Apr 03 '20 at 14:26
@AmitKumar I am heading that way but its so slow for thousands of records — volume one, Apr 03 '20 at 14:48
@volumeone, https://stackoverflow.com/questions/5003028/how-can-i-fill-a-column-with-random-numbers-in-sql-i-get-the-same-value-in-ever, Does it help? — Amit Kumar, Apr 04 '20 at 18:56

score 2 · Answer 1 · answered Apr 03 '20 at 14:31

2

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

answered Apr 03 '20 at 14:31

Nguyễn Văn Phong

13,506
17
39
56

This only works if the values in TempTable are unique. In my table the values are like `100, 100, 100, 100` because a `UserID` can make as many posts as they want. It does not work in this fiddle: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=50bc682cfcb0e18c89ab7c654f7c75e4 – volume one Apr 03 '20 at 14:47
1

@volumeone use some other column in the _inner_ `WHERE` clause. https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=0096fd62af4e407abc443f43e5d42ca3 – Salman A Apr 03 '20 at 14:55

score 0 · Accepted Answer · answered Apr 03 '20 at 15:17

Managed to do it using a CURSOR like this:

DECLARE PostCursor CURSOR 
FOR SELECT PostID FROM dbo.Post WHERE UserID <> 10 AND UserID <> 11 FOR UPDATE
OPEN PostCursor
FETCH NEXT FROM PostCursor 
WHILE @@FETCH_STATUS = 0
BEGIN

     UPDATE dbo.Post 
     SET 
        UserID = (
        SELECT TOP (1) UserID FROM 
        [dbo].[User]
        WHERE UserID IN (1,3,4,7)
        ORDER BY NEWID()
        )

    WHERE CURRENT OF PostCursor
    FETCH NEXT FROM PostCursor
END
CLOSE PostCursor
DEALLOCATE PostCursor

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

2 Answers2