Populate all columns with random int

Question

I'm trying to populate 3 columns all rows with some random ints.
The code below has an error

Incorrect syntax near "@Upper. Expecting ( or Select

What am I doing wrong?

---- Create the variables for the random number generation
DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT

---- Create a random number between 1 and 111
SET @Lower = 1   -- The lowest random number
SET @Upper = 111 -- The highest random number

UPDATE [tblProject]
SET 
       [ContractorID1] = @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0) -- error
      ,[ContractorID2] = @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0) -- error
      ,[ContractorID3] = @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0) -- error

Take out the "@Random = " in your update statement. It doesn't serve any purpose. — pmbAustin, Jan 29 '18 at 18:53
You can post that as an answer if you like. Or I can just delete this question. Which is best to do? — Al Lelopath, Jan 29 '18 at 18:54
actually, now that it works I see something .. each row has the same data. Is there a way to make each row randomly different? Maybe that's better posted as another question. — Al Lelopath, Jan 29 '18 at 18:59
Never mind, there is [a question that address that](https://stackoverflow.com/questions/1045138/how-do-i-generate-random-number-for-each-row-in-a-tsql-select) — Al Lelopath, Jan 29 '18 at 19:05

score 1 · Accepted Answer · answered Jan 29 '18 at 19:10

Take out the "@Random = " in your update statement. It doesn't serve any purpose. You aren't using that variable at all, so you have no need to even declare it. Just do direct assignment.

And as you've discovered, the RAND() function will return the same value for the same row. If you need something different, there are other solutions.

Populate all columns with random int

1 Answers1