DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=ymail.com;
 h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
 b=TfFWbBwMCAetWvM3s86S2PdvW8ulB9QtBfgutvNTb9j/NACic3mNNRXCUsvoTp8vBmHctJytXLAdPnerSB2CREgbSsMEYNPcNw4bGCXrs4NKDI6119SnofsomUwCMA859ZUw5fPtEcYYAWJs9T4lHWGGF5J8QfIP4PEnYJRCNEg=;
References: 
 <CAAXGW-wEWhVvnDshk=z34VqHuEqpNd43XKNAo1b=L5JCVoDbVw@mail.gmail.com>
Message-ID: <1376063055.23129.YahooMailNeo@web162902.mail.bf1.yahoo.com>
Date: Fri, 9 Aug 2013 08:44:15 -0700 (PDT)
From: Kevin Grittner <kgrittn@ymail.com>
Reply-To: Kevin Grittner <kgrittn@ymail.com>
Subject: Re: Efficient Correlated Update
To: Robert DiFalco <robert.difalco@gmail.com>,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
In-Reply-To: 
 <CAAXGW-wEWhVvnDshk=z34VqHuEqpNd43XKNAo1b=L5JCVoDbVw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Robert DiFalco <robert.difalco@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:
>
>=A0=A0 UPDATE contacts SET user_id =3D u.id
>=A0=A0 FROM my_users u=20
>=A0=A0 JOIN phone_numbers pn ON u.phone_significant =3D pn.significant=20
>=A0=A0 WHERE contacts.owner_id =3D 7
>=A0=A0 AND contacts.user_id IS NULL
>=A0=A0 AND contacts.id =3D 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.=20
>
> 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".
=A0
In looking it over, nothing jumped out at me as a problem.=A0 Are you
having some problem with it, like poor performance or getting
results different from what you expected?

--=20
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance