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), > .... > ) Any primary key or unique constraints here? > > 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) I think the UNIQUE above may cause the optimiser to create temporary tables. Do you need it? Have you examined SET EXPLAIN ON output? Do you need more indexes? > > 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. Not sure why you need that. You are trying to update all rows in the table (except the blanks) aren't you? > > I have my doubts if that can even work! But assuming it should: 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) > +------------------------------------------------------------+ > | The expedient performance of a task with excessive concern | > | regarding its duration-to-completion engenders a virtual | > | certainty of diminished benefit therefrom. | > | -- Benjamin Franklin (but he said it in 3 words) | > +------------------------------------------------------------+ You could also do an insert from a select with a join to create a completely new table which can then be used to replace the old one. -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 Mail: Peter.Lancashire.PL1@bayer.co.uk --- My Internet plumbing does not allow me to mail and post news together. Sorry. All opinions are my own and not those of Bayer plc. --- Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/