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)
Jake: How about this:
create a new table, tabc, with the same structure as taba then
select a.*, b.cust_code_b codeb
from taba a, outer tabb b
where a.ta_number = b.ta_number
into temp fred;
insert into tabc
select ta_number, ...(everything but cust_code_a)..., codeb
from fred
where codeb IS NOT NULL;
insert into tabc
select ta_number, ...(everything but codeb)...
from fred
where codeb IS NULL;
drop table taba;rename table tabc to taba;
Art S. Kagel