Обсуждение: multiple sql update w/ major time issues

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.MTDfrom gl_totals awhere 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.YTDfrom gl_totals awhere 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.R12from gl_totals awhere 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!!!!!

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

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
setEXTVALUE217 = 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