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
FROM
app_user u
JOIN
email_addresses e
ON u.email = e.email
WHERE
contact.owner_id = 24
AND contact.user_id IS NULL
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