Skip site navigation (1) Skip section navigation (2)

Need some help with crafting a query to do major update

From: Sean Shanny <shannyconsulting(at)earthlink(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: Need some help with crafting a query to do major update
Date: 2004-02-19 00:51:08
Message-ID: 4034087C.6090606@earthlink.net (view raw or flat)
Thread:
Lists: pgsql-sql
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)


Responses

pgsql-sql by date

Next:From: Stephan SzaboDate: 2004-02-19 03:11:08
Subject: Re: Need some help with crafting a query to do major update
Previous:From: scott.marloweDate: 2004-02-18 21:50:12
Subject: Re: Inserting NULL into Integer column

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group