Update values from subquery for every single row-CodePudding

update users set first_name = (select firstname from temp_names order by random() limit 1),
    last_name = (select lastname from temp_names order by random() limit 1)

Is supposed to get a random row from that temp_names folder, even though it got, but it repeated for every single row it updated, all rows in users had the same first_name value and same last_name value.

How could I make it run the subquery for each row?

CodePudding user response：

The issue is that Postgres decides to do you a favor by optimizing the subquery and running it only once. Although Postgres is not the only database that does this, I consider this an error because the subquery depends on a volatile function. The optimization changes the meaning of the code.

I have found that a correlation clause -- of almost any type -- to the outer query fixes the problem. For instance, if you have a non-NULL id column:

update users u
    set first_name = (select firstname from temp_names tn where u.id is not null order by random() limit 1),
        last_name = (select lastname from temp_names where u.id is not null order by random() limit 1);

Here is a db<>fiddle.