update one table with another

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.pugsley@gmail.com