update table column with different random numbers

Question

I have this query

UPDATE f1
SET col = (SELECT ABS(300 + RANDOM() % 3600))

that update the "col" column with a random number between 300 and 3600. But it returns the same random number for all the rows. Is there a way to update the rows with different random numbers?

Answer 1

It's using the same random number because the subquery only needs to run once for the UPDATE. In other words, the SQL engine knows that the inner SELECT only needs to be run once for the query; it does so, and uses the resultant value for each row.

You actually don't need a subquery. This will do what you want:

UPDATE f1
SET col = ABS(300 + RANDOM() % 3600);

but if for some reason you really do want a subquery, you just need to make sure that it's dependent upon the rows in the table being updated. For example:

UPDATE f1
SET col = (SELECT (col*0) + ABS(300 + RANDOM() % 3600));