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.

Answer 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.

• Abelisto: 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: @Abelisto . . . But why would you use a subquery when one is not necessary?
• Abelisto: I not using subquery and I don't know the OP's logic at this point. I just explain "why" and "how".