Need help with correlated subquery

Question

I have two PostgreSQL tables that each contain a column called date_of_incident.

They each also have a column called incident_id and this is how the tables are related.

I want to update all rows of table 1 by setting date_of_incident equal to date_of_incident from the corresponding row of table 2 where incident_id from table 1 equals incident_id from table 2.

Can anyone can give a sample UPDATE? I'd appreciate it.

incident_id is unique in both tables and it is how the tables are related, though not by foreign key or constraint (sadly). I have the extra condition of status = closed. The only complication is that table 1 has a trigger function on update and I need to examine to see how the SQL will affect things.

Answer 1

Postgres allows to join tables in an UPDATE statement:

update table_1
   set date_of_incident = t2.date_of_incident
from table_2 t2
where table_1.incident_id = t2.incident_id;

This assumes that incident_id is unique in both tables.