Guys, let me know if I have not provided enough information on this post. Thanks!
On Thu, Aug 8, 2013 at 11:06 AM, Robert DiFalco <robert.difa...@gmail.com>wrote: > 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. >
