In my system a user can have external contacts. When I am bringing in external contacts I want to correlate any other existing users in the system with those external contacts. A users external contacts may or may not be users in my system. I have a user_id field in "contacts" that is NULL if that contact is not a user in my system
Currently I do something like this after reading in external contacts:
UPDATE contacts SET user_id =
u.id FROM my_users u
JOIN phone_numbers pn ON u.phone_significant = pn.significant
WHERE contacts.owner_id = 7 AND contacts.user_id IS NULL AND
contacts.id = pn.ref_contact_id;
If any of the fields are not self explanatory let me know. "Significant" is just the right 7 most digits of a raw phone number.
I'm more interested in possible improvements to my relational logic than the details of the "significant" condition. IOW, I'm start enough to optimize the "significant" query but not smart enough to know if this is the best approach for the overall correlated UPDATE query. :)
So yeah, is this the best way to update a contact's user_id reference based on a contacts phone number matching the phone number of a user?
One detail from the schema -- A contact can have many phone numbers but a user in my system will only ever have just one phone number. Hence the JOIN to "phone_numbers" versus the column in "my_users".
Thanks.