By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,991 Members | 1,887 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,991 IT Pros & Developers. It's quick & easy.

Need help with a slow UPDATE query

P: 1
Hi Everyone,

Was hoping you could help me understand what I may be doing wrong. I am trying to update a column of one table from the data in a column of another table. Here are my create statements:

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE dictethnicity(sbirthplace char(15) not null, bplacedesc varchar(100) not null, usstateflag int, ethgrpcode char(5)); 
  2. CREATE TABLE voterethnicity(id bigint, sbirthplace char(15), bplacedesc varchar(100), usstateflag int, ethgrpcode char(5));
The dictethnicity is a lookup table of various ethnicities, each with a unique sbirthplace value. The voterethnicity table is a list of individual voters, who have a unique id value, and a sbirthplace value. I am trying to update the bplacedesc column of the voterethnicity table based on the sbirthplace value shared by both tables. My update query, and the one I having problems with is the following:

Expand|Select|Wrap|Line Numbers
  1. UPDATE voterethnicity SET bplacedesc = de.bplacedesc FROM dictethnicity AS de INNER JOIN voterethnicity AS ve ON (de.sbirthplace = ve.sbirthplace);
Unfortunately, this query never finishes, it just keeps running without ever really updating the table. Below is the output from doing EXPLAIN:

Hash Join (cost=12992818.83..10750351809.52 rows=904238447094 width=324)"
Hash Cond: (ve.sbirthplace = de.sbirthplace)"
-> Seq Scan on voterethnicity ve (cost=0.00..28303.27 rows=1161727 width=64)"
-> Hash (cost=3141736.10..3141736.10 rows=155671418 width=388)"
-> Nested Loop (cost=4.47..3141736.10 rows=155671418 width=388)"
-> Seq Scan on voterethnicity (cost=0.00..28303.27 rows=1161727 width=106)"
-> Materialize (cost=4.47..5.81 rows=134 width=282)"
-> Seq Scan on dictethnicity de (cost=0.00..4.34 rows=134 width=282)"

Any help you all can offer is really appreciated. If there is any more info I can provide, just ask.

Thanks,
CV
Oct 17 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.