0

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.

3

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.

  • I think mine is identical to yours: UPDATE table_1 AS t1 SET date_of_incident = t2.date_of_incident FROM table_2 AS t2 WHERE t2.incident_id = t1.incident_id AND t1.status = 'closed'; Yes, incident_id is unique in both tables and it is how the tables are related, though not by foreign key or constraint (sadly). I added the extra condition of status = closed. So this is called an "UPDATE FROM" query? I will give it a try. 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. – two4two Sep 5 at 0:31
  • @two4two If that answer solved your question then please accept it, so that your question is marked as resolved. – a_horse_with_no_name Sep 5 at 6:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.