Question 259306 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to make a subquery get latest updates during UPDATE statement

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?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    To be able to achieve the results you desire you can use CTEs(Common Table Expressions) This will generate a list of available address IDs then update the organisation table

    WITH available_address_ids AS (
  SELECT id
  FROM address
  WHERE id NOT IN (SELECT address_id FROM organization WHERE address_id IS NOT NULL)
)
UPDATE organization 
SET address_id = (
  SELECT id
  FROM available_address_ids
  ORDER BY random() -- This will select a random available address ID
  LIMIT 1
)
WHERE address_id IS NULL;

    The common table expression(cte) available_address_ids will select all the address IDs that are not already assigned in the organization table.

    Then the update query will select a random available address ID from the CTE and assigns it to the address_id column in the organization table.

    This will ensure that the organization table will contain unique and randomly assigned address id

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search