Correlated Update Query

From: Robert DiFalco <robert(dot)difalco(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Correlated Update Query
Date: 2013-06-02 20:39:20
Message-ID: CAAXGW-x_VzyiYKpqP5rdvfVNhZtGHqZVJRtkqaeSTmaGvuzAQg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Browse pgsql-sql by date

  From Date Subject
Next Message Jayadevan 2013-06-05 04:24:01 Re: crosstab help
Previous Message Rodrigo Rosenfeld Rosas 2013-05-29 16:14:00 Re: [GENERAL] foreign key to multiple tables depending on another column's value