Update with a correlated subquery - YUCCH
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)
+------------------------------------------------------------+
| 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) |
+------------------------------------------------------------+