pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / 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