UPDATE Statement with random values in a column ranging from 1 to 4 only.


I want to insert values in a column (ex: AccountID) in a SQL table, where type is INT.
The values to be inserted in entire table should be from 1 to 4 only.
Which UPDATE statement will help in this requirement?

Sanjay GandhiFounder, KenhalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
Hi Sanjay,

The UPDATE statement to update every row in a table is nearly trivial.  How do you determine which row(s) are assigned 1, 2, 3, or 4?
NorieAnalyst Assistant Commented:

UPDATE TableName SET AccountID = FLOOR(RAND()*4)+1
Sanjay GandhiFounder, KenhalAuthor Commented:
Hi Kent,

Your comment is valid. I can update any row with any number between 1 and 4.  I just need to test certain SQL queries for learning purpose. So I need values between 1 and 4 only, and these will run into some 100 records, therefore repeating values from 1 to 4. In fact any of the random value for any row.

Hi Norie,

Your answer works good with the following:

Declare @NewNum as int
Set @NewNum = Floor(Rand()*4)+1
Select @NewNum

Open in new window

But when I update this with my actual table, I get same result for all records. Whereas I want different result with each row. So if I have 100 rows in the table, then no problem if value 1 or 2 or 3 repeats several times.

CEOs need to know what they should worry about

Nearly every week during the past few years has featured a headline about the latest data breach, malware attack, ransomware demand, or unrecoverable corporate data loss. Those stories are frequently followed by news that the CEOs at those companies were forced to resign.

Sanjay GandhiFounder, KenhalAuthor Commented:
Is there no alternative to (this may sound illogical though):
Update Transactions
Set AccountID = ANY (1,2,3,4)

Open in new window

Kent OlsenDBACommented:
If you can tolerate "organized randomness" this variation on Norie's query should work.

  UPDATE TableName SET AccountID= ((rownum % 4) + 1);

That will assign the rows 1,2,3,4,1,2,3,4,1,2,3,4.....
Sanjay GandhiFounder, KenhalAuthor Commented:
I have Express edition of SQL, and rownum does not work there. I tried this:

UPDATE Transactions
SET AccountID = ((ROW_NUMBER() Over (Order by CustomerID) % 4) + 1)

And this also does not work. Can we pick up milliseconds  and control these to get values between 1 and 4.
NorieAnalyst Assistant Commented:
I found various expressions that purported to produce random numbers, these are the only ones that worked for me.

FLOOR(4 * RAND(CONVERT(varbinary, NEWID())))+1


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sanjay GandhiFounder, KenhalAuthor Commented:
Dear Norie,

This works perfectly for SQL Server Express 2017.

Update Transactions
Set AccountID = FLOOR(4 * RAND(CHECKSUM(NEWID())))+1

It creates random numbers between 1 and 4 for all 100 records, randomly.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.