update fields with random values

[Former Member]

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!

[former_member184768]

Hi Vitalij,

In my opinion, this should be implemented with a cursor and Looping through the cursor records and using RAND() function.

So the procedure way is the right choice.

Regards,

Ravi

[Former Member]

Actually I have even a few millions of different ID's.

So a stored procedure should be the better option. I wrote the following procedure:

CREATE PROCEDURE RandomVALUE()

LANGUAGE SQLSCRIPT AS

v_value INTEGER :='0';

CURSOR c_cursor1 FOR

select "ID", "CHAR", "VALUE"

from "SYSTEM"."TABLE";

BEGIN

FOR cur_row as c_cursor1 DO

IF cur_row.CHAR = 'b' THEN

v_value := (SELECT TO_INT('10' + ('20'-'10')*RAND()) FROM DUMMY);

insert into TABLE2

values (cur_row.ID, cur_row.CHAR, v_value);

END IF;

END FOR;

END;

But it doesn't work and I get following message: "Sub-query is not supported in a stored procedure"

Do you have an idea what I could do?

[Former Member]

Hi,

Just replace

v_value := (SELECT TO_INT('10' + ('20'-'10')*RAND()) FROM DUMMY);

with

v_value :=  (10 + 10*RAND());

Thanks,

Anooj

[former_member184768]

Hi Vitalij,

If I understand your requirement correctly, then I'd suggest few things:

1) If you need to insert only those records where "CHAR" = 'b', then use that restriction in the cursor statement. It will reduce the no of loops for the cursor execution.

2) v_value can be derived as explained by Anooj above.

Regards,

Ravi

[Former Member]

Hi Vitalij,

Not an ideal solution but for the example records you have given I can think of the following to generate different values for Char = 'b':

UPDATE "SYSTEM"."TABLE"

set "VALUE" =

CASE ID

WHEN 1 THEN (select TO_INT('10' + ('20'-'10')*RAND())as VALUE FROM DUMMY)

WHEN 2 THEN (select TO_INT('10' + ('20'-'10')*RAND())as VALUE FROM DUMMY)

END

where "CHAR"='b';

It could still generate the same values if both RAND() functions generate the same number by chance but most often than not you will end up with different values.

Obviously if you have 100s of numbers for column 'ID' it will be tedious to write that many WHEN statements.

Thanks,

Anooj