pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedmultiple sql update w/ major time issues
3+ messages / 3 participants
[nested] [flat]
* multiple sql update w/ major time issues
@ 2001-06-06 15:23 Dawn <iu_23@hotmail.com>
2001-06-07 08:26 ` Re: multiple sql update w/ major time issues Hans-Jürgen Schönig <hs@cybertec.at>
2001-06-12 18:34 ` Re: multiple sql update w/ major time issues Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 3+ messages in thread
From: iu_23@hotmail.com @ 2001-06-06 15:23 UTC (permalink / raw)
To: pgsql-sql
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!!!!!
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: multiple sql update w/ major time issues
2001-06-06 15:23 multiple sql update w/ major time issues Dawn <iu_23@hotmail.com>
@ 2001-06-07 08:26 ` Hans-Jürgen Schönig <hs@cybertec.at>
1 sibling, 0 replies; 3+ messages in thread
From: Hans-Jürgen Schönig @ 2001-06-07 08:26 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: multiple sql update w/ major time issues
2001-06-06 15:23 multiple sql update w/ major time issues Dawn <iu_23@hotmail.com>
@ 2001-06-12 18:34 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2001-06-12 18:34 UTC (permalink / raw)
To: iu_23@hotmail.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2001-06-12 18:34 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-06 15:23 multiple sql update w/ major time issues Dawn <iu_23@hotmail.com>
2001-06-07 08:26 ` Hans-Jürgen Schönig <hs@cybertec.at>
2001-06-12 18:34 ` Tom Lane <tgl@sss.pgh.pa.us>
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