I have an update query that I'm not sure if I am taking a good approach or
a naive one. It works but seems ugly.
I have a table named "contacts". Contacts have a user_id and an owner_id.
There is also a one to many relationship between contacts and email
addresses. Users also have an email address but only one.
When I get a new set of contacts (from an external source) I insert all
those contacts. Then afterwards I want to search users whose email
addresses match those of the new contacts. If there is a match, I want to
update contact so that it points to the user it's email address correlates
to. If the user is already set (i.e. NOT NULL) I don't need to update it
again.
Here is the query:
UPDATE
contact
SET
user_id = u.id
FROM
app_user u
JOIN
email_addresses e
ON u.email = e.email
WHERE
contact.owner_id = 24
AND contact.user_id IS NULL
AND contact.id = e.owner_id
Is there a better way to do this? I'm not great with correlated UPDATE
queries. Seems like this one would do a lot of work.
TIA
