Re: SQL UPDATE question

[Charles Wilt <charles.wilt@xxxxxxxxx>]

What you're trying to do really isn't clear to me, because you are using
the wrong syntax. :)

Given your example data, you only want to update the 1 row in table2 that
has the highest D value for a given (A,B)?
(1, 1, 15, 27) --> (1, 1, 99, 27)

In order to update one table's value from another table, you have to use a
subselect
So initially, something like so...

UPDATE table2
    SET C = ( select table1.C
                         from table1
                         WHERE table2.A = table1.A
                              AND table2.B = table1.B
         );

That would update C in every row in table2 with the value of C from table1.
Note that there must be 1 and only 1 matching row in table1, otherwise
you'd get an error.

So let's consider the rest of your requirement, I think this would give you
what you are looking for
UPDATE table2
    SET C = ( select table1.C
                         from table1
                         WHERE table2.A = table1.A
                              AND table2.B = table1.B
         )
WHERE (A,B, D) in  (SELECT A, B, MAX(D)
                                         FROM table2
                                 GROUP BY A, B);

Honestly, I never used UPDATE for this, now that IBM has provided the MERGE
statement, it's a lot cleaner syntax in my opinion.

MERGE INTO table2 dst
using (select t2.A, t2,B, max(t2.D) as maxD , t1.C
           from table2 t2 join table1 t1
              on  t2.A = t1.A and t2.B = t1.B
            group by t2.A, t2.B, t1.C
          ) src
on dst.A = src.A and dst.B = src.B, dst.d = src.maxd
when matched
   then update set C = src.C;



HTH,
Charles





On Fri, Sep 9, 2022 at 4:12 PM Jack Woehr via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

> What I'm trying to do here is clear though my syntax is wrong.
> What's correct for the UPDATE statement?
>
> create table table1( A int, B int, C int);
> create table table2( A int, B int, C int, D int);
> insert into table1 (A,B,C) values(1,1,99);
> insert into table2 (A,B,C,D) values(1,1,15,27),(1,1,3,13);
>
> UPDATE table2
>     SET table2.C = table1.C
>     WHERE table2.A = table1.A
>     AND table2.B = table1.B
>     AND table2.D = (SELECT MAX(D)
>             FROM table2 dd
>             WHERE dd.A = table1.A
>                   AND dd.B = table1.B
>             GROUP BY dd.A, dd.B);
>
> --
>
> *JACK WOEHR, IBM Champion 2021-2022
> <https://www.credly.com/users/jack-woehr/badges>*
>
> *SENIOR IBM i ENGINEER*
>
>   303.847.8442
>   jwoehr@xxxxxxxxxxxxxxxxxxxxxxxx
>   stay connected
> <https://www.linkedin.com/company/absolute-performance-inc./>
>
> <https://www.absolute-performance.com/>
>  www.absolute-performance.com
>
> NON-DISCLOSURE NOTICE:  This communication including any and all
> attachments is for the intended recipient(s) only and may contain
>
> confidential and privileged information.  If you are not the intended
> recipient of this communication, any disclosure, copying further
>
> distribution or use of this communication is prohibited.  If you received
> this communication in error, please contact the sender and
>
> delete/destroy all copies of this communication immediately.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com