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