pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
multiple 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