Postgresql – Update record column with id found in the table of an associated model

postgresqlupdate

Given three tables:

A users table with id's:

╔════╗
║ id ║
╠════╣
║ 1  ║
╟────╢
║ 2  ║
╟────╢
║ 3  ║
╚════╝

A purposes table with user_id's

╔════╤══════════╗
║ id │ user_id  ║
╠════╪══════════╣
║ 1  │ 2        ║
╟────┼──────────╢
║ 2  │ null     ║
╟────┼──────────╢
║ 3  │ null     ║
╚════╧══════════╝

Where the user_id can be null.

And a journeys table with user_id's and purpose_id's.

╔════╤═════════╤════════════╗
║ id │ user_id │ purpose_id ║
╠════╪═════════╪════════════╣
║ 1  │ 2       │ 3          ║
╟────┼─────────┼────────────╢
║ 2  │ 1       │ 2          ║
╟────┼─────────┼────────────╢
║ 3  │ 1       │ 1          ║
╚════╧═════════╧════════════╝

We want to create a PostgreSQL query that will find all of the purposes with null in their user_id column, select their associated journeys (by selecting them based on the id of said purposes), and then update the then empty user_id column of the purpose with the user_id found in the first associated journey.

What is the best way to do this?

I've gotten as far as being able to find all the user_id's from the journeys table who have purposes with an empty user_id column using an exists subselect query but I'm unsure how to update that null user_id column with the matching user_id found in the journeys table…

select
   user_id
from
   journeys
where exists(
    select
       id
    from
       purposes
    where
       user_id is nil
       and purposes.id = journeys.purpose_id
);

Best Answer

Use the FROM clause in the UPDATE. Like:

UPDATE purposes p
SET    user_id = j.user_id
FROM   journeys j
WHERE  j.purpose_id = p.id
AND    p.owner_id IS NULL
AND    j.user_id IS NOT NULL;  --  may be redundant

Ideally, you have some kind of unique index on journeys(purpose_id) to make this unambiguous. Else multiple rows might qualify.

The last predicate AND j.user_id IS NOT NULL is redundant if the column is defined NOT NULL.