pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: iu_23@hotmail.com
Cc: pgsql-sql@postgresql.org
Subject: Re: multiple sql update w/ major time issues
Date: Tue, 12 Jun 2001 14:34:00 -0400
Message-ID: <12898.992370840@sss.pgh.pa.us> (raw)
In-Reply-To: <aaac9c0b.0106060723.57cfa53a@posting.google.com>
References: <aaac9c0b.0106060723.57cfa53a@posting.google.com>

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



view thread (3+ messages)

Message-ID: <12898.992370840@sss.pgh.pa.us>
Permalink:  ../12898.992370840@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/12898.992370840@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, iu_23@hotmail.com
  Subject: Re: multiple sql update w/ major time issues
  In-Reply-To: <12898.992370840@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox