update one table with another - Mailing list pgsql-general

From Matthew Pugsley
Subject update one table with another
Date
Msg-id 873251980904201323s3e309bbeve50e543d14b642ea@mail.gmail.com
Whole thread Raw
List pgsql-general
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,

pgsql-general by date:

Previous
From: ray
Date:
Subject: Re-Install data folder failure
Next
From: Bill Moran
Date:
Subject: Re: Doubt about join clause