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
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 |