Postgres UPDATE FROM set value according to result of other query

Question

I'm stuck with an UPDATE. My tables originally were:

Clients
------------------
ID
Emails
Notification_type

Contracts
------------------
ID
Client_id (FK)

Contracts_document_types
------------------
Contract_id (FK)
Document_type_id (FK)

And I could notify basing on Clients table. Then I had to change the notification method. My database changed to:

Contracts_document_types
------------------
Contract_id (FK)
Document_type_id (FK)
Notification_method_id (FK)

Notification_methods
------------------
ID
emails
notification_type

I was able to create the necessary notification_methods entries based on clients, but I didn't find a way to associate in contracts_document_types.notification_method_id. It is possible to find the notification_method based on clients table, but the table to be updated is contracts_document_types and I cannot figure out how.

My idea is something like:

update contracts_document_types
set notification_method_id = query.nm_id
from ( select NM.id as nm_id from notification_methods NM
       inner join clients C on C.emails = NM.emails 
             and C.notification_type = NM.notification_type    
) as query
where ????????

It's possible to get to contracts_document_types from clients (where the information lies), but I could not associate them to the right users. I'm missing something important about the iteration process in UPDATE FROM statement.

Resume: 1 client has many contracts_document_types, I have an notification_method created based on clients. I cannot find a way to put the right notification_method on each client (all contracts_document_types from that client).

Could you give some examples of clients, their contracts, and a short description how do you find a notification for this example data ? — krokodilko, May 12 '15 at 21:10
Consider using temporary tables to make the very update statement as simple as possible. I would say that using subqueries in updates is not a good idea, because it's more difficult to understand and it's mistake-prone... — percy, May 12 '15 at 21:21
@voytech: On the upside: as soon as you *do* understand it, it magically transforms into an excellent idea. — Erwin Brandstetter, May 13 '15 at 01:55
Exactly. Anyone may resolve it creating a script in any programming language or using temporary tables, is not a bad idea, but this statement seems pretty strong, the time spent strugling will pay off soon. — Pedro Ivan, May 18 '15 at 14:02

score 2 · Accepted Answer · edited May 23 '17 at 12:21

My educated guess, it should work like this:

UPDATE contracts_document_types cd
SET    notification_method_id = q.nm_id
FROM  (
   SELECT co.id AS co_id, nm.id AS nm_id
   FROM   contracts co
   JOIN   clients   cl ON cl.id = co.client_id
   JOIN   notification_methods nm USING (emails, notification_type)
   ) q
WHERE  cd.contract_id = q.co_id;

You just forgot to add the contracts table as missing link. Details for UPDATE in the manual.

USING is just a syntax shorthand. If emails and notification_type can be ambiguous, be explicit like you had it:

JOIN   notification_methods nm ON nm.emails = cl.emails
                              AND nm.notification_type = cl.notification_type

Simpler yet, you don't even need a subquery:

UPDATE contracts_document_types cd
SET    notification_method_id = nm.id
FROM   contracts co
JOIN   clients   cl ON cl.id = co.client_id
JOIN   notification_methods nm USING (emails, notification_type)
WHERE  cd.contract_id = co.id;

Worked as you posted, just the cd abbreviation on update wasn't working, I took off and it ran. Thank you sir. — Pedro Ivan, May 18 '15 at 13:56
@PedroIvan Great! The *table alias* `cd` should work, too ([see documentation](http://www.postgresql.org/docs/current/interactive/sql-update.html)) - unless you are on a very old version of Postgres. That feature was introduced with Postgres 8.2. It's just a syntax shortcut for convenience here and totally optional. — Erwin Brandstetter, May 18 '15 at 14:02

Postgres UPDATE FROM set value according to result of other query

1 Answers1