Re: SQL UPDATE question -- MIDRANGE-L

Charles, thank you. That worked after I corrected some typos. You are an
SQL ace to have tossed that off from the top of the head!!

For the mailing list archives, here's the full working example of updating
one row in Table2 with the C value from Table 1 .IFF. the Table2 row has
the max D value of all Table2 rows that match Table1 on the A and B values:

*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),(1,1,0,45), (1,1,0,27),
(1,1,4,54);-- select * from table1;-- select * from table2;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 AND
dst.D = src.maxD WHEN MATCHED THEN UPDATE SET C = src.C;-- select * from
table1;-- select * from table2;*

On Sat, Sep 10, 2022 at 10:11 AM Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:

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

--
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