From: Hans-Jürgen Schönig <hs@cybertec.at>
To: pgsql-sql@postgresql.org
Subject: Re: multiple sql update w/ major time issues
Date: Thu, 07 Jun 2001 10:26:42 +0200
Message-ID: <3B1F3AC2.7E9A330D@cybertec.at> (raw)
References: <aaac9c0b.0106060723.57cfa53a@posting.google.com>
Dawn schrieb:
> 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). Here is how I have it
> right now:
>
> update aud_member_ext_attributes b
> set EXTVALUE217 =
> (select a.MTD
> from gl_totals a
> where a.category = 'tankrent'
> and a.CUST_NO = b.EXTVALUE101
> and a.DIST_NO = b.EXTVALUE102
> and a.SUB_NO = b.EXTVALUE105
> and a.FUEL_TYPE = b.EXTVALUE123);
>
> update aud_member_ext_attributes b
> set EXTVALUE223 =
> (select a.YTD
> from gl_totals a
> where a.category = 'tankrent'
> and a.CUST_NO = b.EXTVALUE101
> and a.DIST_NO = b.EXTVALUE102
> and a.SUB_NO = b.EXTVALUE105
> and a.FUEL_TYPE = b.EXTVALUE123);
>
> update aud_member_ext_attributes b
> set EXTVALUE229 =
> (select a.R12
> from gl_totals a
> where a.category = 'tankrent'
> and a.CUST_NO = b.EXTVALUE101
> and a.DIST_NO = b.EXTVALUE102
> and a.SUB_NO = b.EXTVALUE105
> and a.FUEL_TYPE = b.EXTVALUE123);
>
> There are 3 "extvaluexxx" that are set for each "category" for a total
> of 9 categories. This makes a grand total of 27 update statements.
> Any suggestions? It would be much appreciated!!!!!
Is there no way to do it in one statement?
Try something like that:
update aud_member_ext_attributes b
set EXTVALUE223 =
(select a.YTD
from gl_totals a
where a.category = 'tankrent'
and a.CUST_NO = b.EXTVALUE101
and a.DIST_NO = b.EXTVALUE102
and a.SUB_NO = b.EXTVALUE105
and a.FUEL_TYPE = b.EXTVALUE123),
EXTVALUE229 =
(select a.R12
from gl_totals a
where a.category = 'tankrent'
and a.CUST_NO = b.EXTVALUE101
and a.DIST_NO = b.EXTVALUE102
and a.SUB_NO = b.EXTVALUE105
and a.FUEL_TYPE = b.EXTVALUE123);
You can update multiple columns with just one update.
Hans
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: hs@cybertec.at
Subject: Re: multiple sql update w/ major time issues
In-Reply-To: <3B1F3AC2.7E9A330D@cybertec.at>
* 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