Update table using different random values for each update row

Question

I want to update random 20% values of a table in Postgres, I want to assign for this attribute the old attribute + a random number with specific limits, and I want that for each row this random number must be different.

I am currently doing this:

update tab_ex
set val = (SELECT val + (SELECT random()*2000 FROM generate_series(20,2000) LIMIT 1))
where id in (select id from tab_ex order by random() limit (select count(*)*0.2 from tab_ex));

and it is updating 20% of my table however it is updating with a specific random number for every row instead of changing this random number for each update.

Gordon Linoff · Accepted Answer · 2016-05-10 21:45:17Z

1

I have seen this problem in other databases, where a subquery gets "optimized away" even though it has a volatile function in it. That may be happening here. One possibility is to remove the subquery:

update tab_ex
    set val = val + random() * 2000
    where id in (select id
                 from tab_ex
                 order by random()
                 limit (select count(*)*0.2 from tab_ex)
                );

This should re-run random() for every row being updated.

answered May 10, 2016 at 21:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

Abelisto Over a year ago

It is because the internal subquery SELECT random()*2000 FROM generate_series(20,2000) LIMIT 1 is not depending on the updating row data. Changing it to SELECT random()*2000 + val FROM generate_series(20,2000) LIMIT 1 for example also solves the problem.

Gordon Linoff Over a year ago

@Abelisto . . . But why would you use a subquery when one is not necessary?

Abelisto Over a year ago

I not using subquery and I don't know the OP's logic at this point. I just explain "why" and "how".

Collectives™ on Stack Overflow

Update table using different random values for each update row

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related