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 theUPDATE
. Like: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 definedNOT NULL
.