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