Re: Update with a correlated subquery - YUCCH

In article <3550D117.DECC64C6@garpac.com>, Jacob Salomon <jake@garpac.com> writes >Hi Family. > >I am hoping someone can come up with a better SQL than this. > >Refined to its simplest levels: > >I have 2 tables: taba and tabb. Among their respective columns, are the >following: > >create table taba >( ta_number integer, > cust_code_a char(5), > .... >)> >create table tabb >( ta_number integer, > .... > cust_code_b char(5), > .... >)> >In both tables, ta_number already has a value in all rows. Also, in >table taba, just about all rows hava a value in cust_code. > >It is my desire to copy the cust_code from taba to tabb where the >ta_number values match up. > >The update statement I came up with is the following: > >update tabb > set cust_code_b > = (select unique cust_code_a > from taba > where taba.ta_number = tabb.ta_number) > >Note that this is somewhat simplified. For example, there is a where >clause on the tabb outside the subquery to cut down on the number of >rows examined for need to update. > >I have my doubts if that can even work! But assuming it should: > >As you can see, this uses a correlated subquery. When I tested it >against temp tables, I watched as the number of locks my process held >grew s-l-o-w-l-y, like 1 every 10 seconds. I also noted that, (assuming >each lock represented 1 updated temp row) with 500 rows updated out >11,000 I had already taken up about 20% of the available log space as >the engine builds and drops a temp table for every row in the outer >query. (Yeah, log space happens to be less than adequate. And *this* >query must run on a 5.x system, so TEMPSPACE and temp dbspaces are not >an option for avoiding the logging of temp tables.) > >Does anyone out there have a better idea for accomplishing this update? >C'mon, there HAS to be something better than that! > Use a CURSOR WITH HOLD in 4GL? >Thanks. -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care