I am trying to update all rows for a specific column in a table with a random number between 1-3 inclusive. I've tried using this code:
UPDATE [dbo].[MyTable]
SET RandColumn = FLOOR(RAND() * (3 - 1 + 1)) + 1
The problem with this is that the random number is generated once and used in each row.
How do I change this so that it creates the random number for every row?
Including example DDL and DML will make this, and questions like it, much easier to solve. Consider:
DECLARE @Table TABLE (Column1 INT, Column2 DATE, ...); INSERT INTO @Table (Column1, Column2, ...) VALUES (1, '2024-01-01', ...), (2, '2024-02-01', ...);
In addition please tag the appropriate SQL product. The answer may be different for various dialects (SQL Server, Oracle, PostGreSQL, etc).
Assuming you're using SQL Server, consider the following:
/* Create some demo data */
DECLARE @Table TABLE (ID INT, RandomValue INT);
INSERT INTO @Table (ID)
SELECT object_id
FROM sys.tables;
/* Update the table using a random value between 1 and 3 for each row */
UPDATE c
SET RandomValue = a.a
FROM @Table c
CROSS APPLY (VALUES (CAST(ROUND(((3 - 1) * RAND(CONVERT(VARBINARY,NEWID(),1))) + 1,0) AS INT))) a(a);
/* review the result */
SELECT a.ID, a.RandomValue
FROM @Table a;
| ID | RandomValue |
|---|---|
| 901578250 | 2 |
| 933578364 | 2 |
| 981578535 | 1 |
| 1006626629 | 1 |
| 1022626686 | 3 |
| 1029578706 | 3 |
| 1038626743 | 3 |
| 1054626800 | 2 |
| 1077578877 | 2 |
| 1125579048 | 3 |
| 1189579276 | 2 |
| 1253579504 | 2 |
| 1285579618 | 2 |
| 1333579789 | 2 |
| 1365579903 | 2 |
| 1445580188 | 2 |
| 1525580473 | 2 |
| 1557580587 | 2 |
| 1726629194 | 1 |
| 1742629251 | 2 |
| 1758629308 | 2 |
| 1790629422 | 2 |
This works by creating a random value per row using CROSS APPLY.