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

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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.