How to make a subquery get latest updates during UPDATE statement

Question

I have two tables, organization and address.

organization table has an address_id column but all the rows have address_id set to NULL.

I want to SET address_id in organization table equal to a random id in address table but I also want all the ids to be unique.

This is what I tried:

UPDATE organization
SET address_id = (SELECT id
                  FROM address
                  WHERE id NOT IN (SELECT address_id FROM organization WHERE address_id IS NOT NULL)
                  LIMIT 1)

But the subquery is not getting the updated values after each update.

What I'm expecting/trying to do:
Assuming on the first update sets address_id to 6, then on the next update 6 should not be returned since 1 organization already has the address_id 6.

But all rows in organization end up having the same address_id.
How can I go about this?

Answer 1

This query assigns the next free address ID for each to each organization that does not have one, yet:

UPDATE organization
SET    address_id = a1.id
FROM  (
   SELECT a.id, row_number() OVER () AS link_id  -- arbitrary number (not strictly random)
   FROM   address a
   WHERE  NOT EXISTS (SELECT FROM organization o WHERE o.address_id = a.id)
   ) a1
JOIN (
   SELECT o.id, row_number() OVER () AS link_id  -- arbitrary number (not strictly random)
   FROM   organization o
   WHERE  address_id IS NULL
   ) o1 USING (link_id)
WHERE  o1.id = o.id;

Prepare the assignments in subqueries in the FROM clause to the UPDATE.
Read the manual on UPDATE here.

Assuming no concurrent write access. Else you have to lock both tables to be sure.

If there are not enough free addresses, some organizations keep address_id IS NULL.