Menu
I want to update the column ItemValue of table Items with a decimal value generated randomly within 1 and 100 (a different value for each row). Each value should have two (random) digits.
CREATE TABLE Items
(
ItemID int IDENTITY(1,1) NOT NULL,
ItemValue decimal(13, 4) NULL,
CONSTRAINT PK_Items PRIMARY KEY CLUSTERED (ItemID ASC)
)
INSERT INTO Items(ItemValue) VALUES (0)
INSERT INTO Items(ItemValue) VALUES (0)
INSERT INTO Items(ItemValue) VALUES (0)
INSERT INTO Items(ItemValue) VALUES (0)
-- Now, I want to update the table
623
SQL RAND() exampleSELECT RAND(); SELECT RAND(5); As you can see, in the first example, the RAND() function generates a random number in decimals between 0 to 1. Each time you execute the statement, it returns different random values.
To create a random integer number between two values (range), you can use the following formula: SELECT FLOOR(RAND()*(b-a+1))+a; Where a is the smallest number and b is the largest number that you want to generate a random number for.
The ROUND() function rounds a number to a specified number of decimal places.
The usual way is to use the modulo of the checksum. Note that checksum(newid()) can produce -2,147,483,648 and cause integer overflow on abs() , so we need to use modulo on the checksum return value before converting it to absolute value. This generates a random number between 0 and 9999.
You can use RAND to generate random number. But there is one problem - RAND is executed only once per query, so all your rows will contain same random value. You can use CHECKSUM(NEWID()) to make it random per row, like this
UPDATE items
SET itemValue = ROUND(RAND(CHECKSUM(NEWID())) * (100), 2)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
