Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5CIY3E08743 for ; Tue, 12 Jun 2001 14:34:03 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.3/8.11.3) with ESMTP id f5CIY0012901; Tue, 12 Jun 2001 14:34:00 -0400 (EDT) To: iu_23@hotmail.com (Dawn) cc: pgsql-sql@postgresql.org Subject: Re: multiple sql update w/ major time issues In-reply-to: References: Comments: In-reply-to iu_23@hotmail.com (Dawn) message dated "06 Jun 2001 08:23:21 -0700" Date: Tue, 12 Jun 2001 14:34:00 -0400 Message-ID: <12898.992370840@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200106/184 X-Sequence-Number: 3436 iu_23@hotmail.com (Dawn) writes: > I have a sql update statement that is dealing with hundreds of > thousands of records. It takes hours and hours to complete (if it > does complete and not take down the server). No wonder, if you write it like that. You're forcing a fresh select from gl_totals for each row of aud_member_ext_attributes; then you're doing it again for each UPDATE command. If you were using Postgres (which I surmise you are not, because it doesn't accept aliases in UPDATE) you could do update aud_member_ext_attributes set EXTVALUE217 = gl_totals.MTD, EXTVALUE223 = gl_totals.YTD, EXTVALUE229 = gl_totals.R12 where gl_totals.category = 'tankrent' and gl_totals.CUST_NO = EXTVALUE101 and gl_totals.DIST_NO = EXTVALUE102 and gl_totals.SUB_NO = EXTVALUE105 and gl_totals.FUEL_TYPE = EXTVALUE123; regards, tom lane