× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.