Re: Update with a correlated subquery - YUCCH

Jacob Salomon wrote: > > 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! > > Thanks. > -- > -- Jake (Retrospectively realizes there is no future in hindsight) Hi Jake, how about selecting the rows from taba in a loop and providing the field values to the update statement. You can write a SP that does this, something like the following lines: FOREACH SELECT ta_number,cust_code_a INTO l_ta_number,l_cust_code_a FROM taba UPDATE tabb SET cust_code_b = l_cust_code_a WHERE ta_number = l_ta_number; END FOREACH HTH Tolis -- V+K Relational Solutions mailto:tvarnas@compulink.gr Deligiorgi 26 mailto:tvarnas@orbit.de 546 42 Thessaloniki Voice: +30-31-820270 Greece Fax: +30-31-865463