Re: multiple sql update w/ major time issues

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