Update column with random values from a list in SQL Server

Question

I have a column which I want to update randomly with a value that is either 1, 2, 3, 4, 5 or 6. Can anyone help please?

Answer 1

+1

This should get you what you need.

CREATE TABLE
    #rando
(
    id bigint NOT NULL 
      PRIMARY KEY
      IDENTITY,
    rando tinyint
);

INSERT
    #rando WITH (TABLOCK)
(
    rando
)
SELECT TOP (1000)
    rando = 0
FROM sys.messages AS m;

UPDATE r
 SET r.rando = ABS(CHECKSUM(NEWID()) % 6) + 1
FROM #rando AS r;

SELECT
    r.*
FROM #rando AS r
ORDER BY
    r.id

Answer 2

This Link has some detailed information. In your case, you can try leveraging the RAND() function, and CROSS APPLY as well as NewId() to generate a random value. Using RAND() by itself will apply the same random value to all values in the update statement.

Example SQL:

/**********************************************************************************************************************
** Create sample Table
**********************************************************************************************************************/
DROP TABLE IF EXISTS #Foobar;
CREATE TABLE #Foobar
(
    StaticVal nvarchar(10),
    RandomValue int
);

INSERT INTO #Foobar
(
    StaticVal
)
VALUES
('Hello'),
('World'),
('Sample'),
('Data');

/**********************************************************************************************************************
** Updating Existing Table
**********************************************************************************************************************/
UPDATE
    F
SET
    F.RandomValue = R.Val
FROM #Foobar AS F
CROSS APPLY
(SELECT Val = ABS (CHECKSUM (NEWID ())) % 6 +1) AS R;


/**********************************************************************************************************************
** Select From Table
**********************************************************************************************************************/
SELECT * FROM #Foobar AS F;