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...

where exists(
       user_id is nil
       and purposes.id = journeys.purpose_id

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.

  • Good point on the unique index. I'm expecting that the rows found in the journeys table with a given purpose_id will all have the same user_id. Is there a database constraint that I can use to enforce this? – dynsne Oct 24 '18 at 18:33
  • @dynsne: A UNIQUE constraint would prevent duplicates. If you already have duplicates, define which row to pick from each set of dupes in a subquery. There are various techniques. See: stackoverflow.com/a/7630564/939860 – Erwin Brandstetter Oct 24 '18 at 20:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.