Thread: Need some help with crafting a query to do major update

To all,

This is part of a data warehouse.  Made the mistake of using a natural 
key in one of the fact tables.  :-(  The f_test_pageviews is a simple 
testing table while I work this out.  The real table has an identical 
schema.

I have built a mapping table, d_user, to allow the replacement of the 
text based (32 characters wide) subscriber_key in f_test_pageviews with 
an int4 mapping key.  I need to replace all of the 
f_test_pageviews.subscriber_key values with the d_user.id value putting 
it in f_test_pageviews.sub_key column.

I have tried this sql:

update f_test_pageviews set sub_key = t2.id from f_test_pageviews t1, 
d_user t2 where t1.subscriber_key = t2.user_id;

but it is taking forever to complete. I would appreciate it if anyone 
could tell me a faster way to do this.  I have to update 250 million 
plus rows over 4 tables. (We break the page view tables into calendar 
months)

Thanks.

--sean



Table "public.d_user"Column  |  Type   |                       Modifiers
---------+---------+--------------------------------------------------------id      | integer | not null default
nextval('public.d_user_id_seq'::text)user_id| text    | not null
 
Indexes:   "d_user_pkey" primary key, btree (id)   "d_user_user_id_key" unique, btree (user_id)

Table "public.f_test_pageviews"        Column         |  Type   | Modifiers
------------------------+---------+-----------id                     | integer |date_key               | integer
|time_key              | integer |content_key            | integer |location_key           | integer |session_key
    | integer |subscriber_key         | text    |persistent_cookie_key  | integer |ip_key                 | integer
|referral_key          | integer |servlet_key            | integer |tracking_key           | integer |provider_key
    | text    |marketing_campaign_key | integer |orig_airport           | text    |dest_airport           | text
|commerce_page         | boolean |job_control_number     | integer |sequenceid             | integer |url_key
    | integer |useragent_key          | integer |web_server_name        | text    |cpc                    | integer
|referring_servlet_key | integer |first_page_key         | integer |newsletterid_key       | text    |sub_key
    | integer |
 
Indexes:   "idx_temp_pageviews_id" unique, btree (id)

On Wed, 18 Feb 2004, Sean Shanny wrote:

> To all,
>
> This is part of a data warehouse.  Made the mistake of using a natural
> key in one of the fact tables.  :-(  The f_test_pageviews is a simple
> testing table while I work this out.  The real table has an identical
> schema.
>
> I have built a mapping table, d_user, to allow the replacement of the
> text based (32 characters wide) subscriber_key in f_test_pageviews with
> an int4 mapping key.  I need to replace all of the
> f_test_pageviews.subscriber_key values with the d_user.id value putting
> it in f_test_pageviews.sub_key column.
>
> I have tried this sql:
>
> update f_test_pageviews set sub_key = t2.id from f_test_pageviews t1,
> d_user t2 where t1.subscriber_key = t2.user_id;

I don't think the above does what you want because I don't think you meant
to be joining f_test_pageviews in twice (once as the table to be updated
and once as t1) or at least not without limiting which rows you want to
update.

I think you probably just want:
update f_test_pageviews set sub_key=t2.id from d_user t2 wheref_test_pageviews.subscriber_key=t2.user_id;