Random value insertion in a table for specific range

Question

I have a table named Orders and a column in that table named OrderID. Initially the value for the OrderID is given manually, but now I have to change those values to random of numbers between 100 and 999.

I have nearly 600 rows... When I tried the following code it assigned a single random number to all the orders. Kindly help me on this, and explain me what's the easy and right way to solve this.

UPDATE Orders
 SET OrderID= RAND()*(999-100)

Rahul Tripathi · Accepted Answer · 2015-04-09T11:53:56.283

0

Try like this:

UPDATE Orders 
SET OrderID = ABS(Checksum(NewID()) % 900) + 100

SQL FIDDLE DEMO

edited Apr 09 '15 at 11:53

answered Apr 09 '15 at 11:11

Rahul Tripathi

168,305
31
280
331

Your code works fine but the exceeds 999... like 1061,1088 like that. – Gopalakrishnan Cassie Apr 09 '15 at 11:23
@Gopalakrishnanyadav:- Try this: `UPDATE Orders SET OrderID = ABS(Checksum(NewID()) % 999) + 100` – Rahul Tripathi Apr 09 '15 at 11:27
i tried with the new code but Same issue – Gopalakrishnan Cassie Apr 09 '15 at 11:42
@Gopalakrishnanyadav:- Try this: `UPDATE Orders SET OrderID = ABS(Checksum(NewID()) % 900) + 100` – Rahul Tripathi Apr 09 '15 at 11:46
issue solved can you explain what the cause of problem by the existing codes .. – Gopalakrishnan Cassie Apr 09 '15 at 11:49
@Gopalakrishnanyadav:- The modulo operator was causing the issue. You need to get the modulo with 900. Thanks and do accept the answer if that helped you. The tick mark which turns green when clicked. – Rahul Tripathi Apr 09 '15 at 11:51

Random value insertion in a table for specific range

1 Answers1