Hello, I am looking for a way to update one table with another. I tried the following schema to update table2 based on data in table1. The idea is that I have a slowly changing dimension and I need to update data in the dimension based on an updated version of the table. I don't want to have to drop my foreign key constraints, delete all the data, copy new data, and then re-establish the constraints.
The schema (taken from forums.devshed.com): update table2 set link = t1.link from table2 t2 inner join table1 t1 on t2.name = t1.name Applying to my own situation: update entities set customer_status = t1.customer_status from entities t2 inner join entity_dimension_update t1 on t2.entity_id = t1.entity_id (where entitiy_id is my primary key and customer_status is a varchar(11) and entity_dimension_update is a structural copy of entities) I'm not sure I entirely follow the logic here. I understand that the table being updated is somehow joined with the table in the from clause. I suppose that by including table2 itself as part of a joined table in the from clause, something like a join between the updated table and the updating table is accomplished (joined on a key to ensure a unique update value). The query ran for half an hour before I cancelled it. I am using Postgres 8.3. The only constraint on either table is the primary key constraint on entity_id. Each table has 62960 rows. Is there something I can do to speed this process up? Thank you, Matt -- matthew.pugs...@gmail.com
