How to update a table column with random values within a range in Oracle?

Question

I have a table with 3 columns: - PRIMARY_KEY - AMOUNTS - RAND_AMOUNTS (all nulls)

I want the RAND_AMOUNTS column to be populated with following formula:

AMOUNT*(0-100 random value)/100

So for example if the amount row let us suppose is 10 and the random value generated for the row is 10 then RAND_AMOUNT should be 10 * 10 / 100 = $1

user330315 · Accepted Answer · 2011-04-26 16:49:14Z

8

dbms_random() is what you are looking for:

UPDATE the_table
   SET rand_amount = (amount * dbms_random.value(0, 100) ) / 100
WHERE amount IS NOT NULL

answered Apr 26, 2011 at 16:49

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

venky80 Over a year ago

Thanks that worked perfectly :) How come i am not seeing precision in my rand_amount all of them are whole numbers hmm.

Craig Over a year ago

@venky80 - check the rand_amount column to see if it is defined with a scale of 0 (e.g. number(9,0) ). If so, it will only store whole numbers as scale is the number of digits allowed after decimal.

HAL 9000 · Accepted Answer · 2011-04-26 16:53:11Z

4

update yourtable set rand_amounts = dbms_random.value * amount;

answered Apr 26, 2011 at 16:53

HAL 9000

3,9851 gold badge25 silver badges29 bronze badges

2 Comments

Jeffrey Kemp Over a year ago

+1 nicely done. This deserves more upvotes. @hal9000, it was probably downvoted by someone who doesn't realise that dbms_random.value returns the same thing as dbms_random.value(0,100)/100. Them's the breaks :)

Olivier Faucheux Over a year ago

And for integers between 1 and 4 (inclusive): ceil(dbms_random.value() * 4)

cagcowboy · Accepted Answer · 2011-04-26 16:49:22Z

2

Using:

dbms_random.value(lower-value-limit, upper-value-limit)

should do what you're looking for.

answered Apr 26, 2011 at 16:49

cagcowboy

31.1k11 gold badges75 silver badges95 bronze badges

Collectives™ on Stack Overflow

How to update a table column with random values within a range in Oracle?

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related