Skip to Content
0
Former Member
Oct 28, 2012 at 02:28 PM

update fields with random values

276 Views

Hello Community

I need to update a few fields in a table with some random integer values...

My table have the following structure

ID CHAR VALUE

1 a 2

1 b 4

1 c 6

2 a 5

2 b 9

2 c 1

I want to update the field "VALUE" of each row where the "CHAR" is 'b' with a random value between 10 and 20

I thought of following command

UPDATE "SYSTEM"."TABLE"

set "VALUE = "A"."VALUE"

from (select TO_INT('10' + ('20'-'10')*RAND())as VALUE FROM DUMMY) as A

where "CHAR"='b'

the random function is working but now i get each row (with "CHAR"='b') with the same random value filled.

for example:

ID CHAR VALUE

1 a 2

1 b 15

1 c 6

2 a 5

2 b 15

2 c 1

I think, that i need to write a stored procedure instead of a simple SQL query, because there i can use a loop... Is it right or dows someone has another idea? Could some help me to write these procedure?

Thank you!