Re: SQL update a column from another table, matching RRNs

I a big fan of SQL, but this is one case where using RPG is your best bet...

SQL doesn't deal well with operations by RRN. (it causes full table scans)

There's no sense expanding your knowledge of SQL with a bad idea.

If you really want to do it, you're probably going to need a temporary table.

declare global temporary table mytbl
as (select rrn(a) as rrn_a, a.cmcust as cust_a,
rrn(b) as rrn_b, a.cmcust as cust_b
from lib1/customer a join lib2/customer b
on rrn(a) = rrn(b)
) with data;

update lib1/customer a
set cmcust = (select cust_b from mytbl z
where a.cmcust = z.cust_a);

HTH,
Charles


On Tue, Dec 21, 2010 at 12:00 PM, Dan <dan27649@xxxxxxxxx> wrote:

I messed up a column of my test data.  I have another table that has the
correct data I would like to fix the original table with.  Since the column
was a key field I messed up, I have to match the records by RRN.  (Other
values in the matching records are different, and this is why I can't just
do a straight CPYF.)  I've tried several variations of the following:

update Lib1/Customer a
  Set a.cmcust = (select z.cmcust from Lib2/Customer z
                             where RRN(a) = RRN(z)       )

In all cases, I'm getting SQL5001 (Column qualifier or table A undefined.)

I know I can write an RPG program to do this in probably 5 minutes, but am
asking here to try to expand my working knowledge of SQL.

TIA,
- Dan
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.