Update all rows in table to have random number for a column-CodePudding

update payment set order_number = (SELECT string_agg (substr('123456789123456789123456789123456789123456789123456789', ceil (random() * 12)::integer, 10), ''))

I tried something like this but result is ALL the rows have the SAME random number. What I want is random number for each row.

CodePudding user response：

The uncorrelated subquery gets a separate query plan, is evaluated once and reused. Just strip the pointless subquery wrapper, and the volatile function random() will be called once per row as desired:

UPDATE payment
SET    order_number = substr('123456789123456789123456789123456789123456789123456789', ceil(random() * 12)::int, 10);

Aside: Why ceil(random() * 12)? That produces numbers starting with 2,3,4 being twice as often as the rest. Should probably be ceil(random() * 9) (since there is no 0 in your string) for a random distribution.

CodePudding user response：

The uncorrelated subquery is only run once, despite being volatile. To fix it you need to mix in some of the outer query in a way that doesn't change the result. Then it will be a correlated subquery, and will execute once per each row. Something like this should do it:

update payment set order_number = (
    SELECT concat(
      substr('123456789123456789123456789123456789123456789123456789', ceil (random() * 12)::integer, 10), 
      substr(order_number::text,1,0)
    )
)

I removed the string_agg, which seemed kind of pointless on a subquery with no FROM. (on the other hand, the whole subquery seems pointless and could be replaced by just an expression, in which the volatility would be detected and it would act appropriately without any tricks).