Re: Update with a correlated subquery - YUCCH

I, Jacob Salomon, asked for help: > 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) Some folks tried to help me with ideas I had already rejected - unloading part of the table ideas and the like. Peter pointed out that this is a good case for 4GL. Others (like Apostolos) pointed out that this is a situation for a stored procedure. Upon seeing this solutions, I said to myself "Ayyyy STOOOOPID!" Sometimes you (OK, I) get so bound up in one approach as to be blinded to simpler ideas. Yes, it was best solved with procedural statements. I went with the 4GL because that can be debugged and rolled back before the actual update is committed. Thanks for removing the blinders! BTW, in 12 years of 4GL programming (I used release 1 in 1986) I have never before run across a rather simple restriction: I cannot use an ORDER BY when preparing a SELECT .. FOR UPDATE statement. Perhaps this was lifted after 4.1x but I rather doubt it. -- -- 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) | +------------------------------------------------------------+