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

Sanjay Gandhi
Sanjay Gandhi used Ask the Experts™
on
Hi,

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?

San.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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:
Perhaps.

UPDATE TableName SET AccountID = FLOOR(RAND()*4)+1
Sanjay GandhiFounder, Kenhal

Author

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.

-San.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Sanjay GandhiFounder, Kenhal

Author

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

Open in new window

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, Kenhal

Author

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.
Analyst 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

FLOOR(4 * RAND(CHECKSUM(NEWID())))+1
Sanjay GandhiFounder, Kenhal

Author

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.

Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial