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
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,
