Re: SQL update column join on same table -- MIDRANGE-L

Your example is a bit confusing because you are using the same table and
not your dependent ones.

https://www.ibm.com/docs/en/i/7.4?topic=language-merging-data

This link above gives you, not exactly what you want but the idea is there
for the update.

In a approach to your code, that shouldn't work, UNLESS table B is another
table in another library, I would rewrite your sql above like this:

MERGE INTO MYLIB.MYTABLE AS A USING MYLIB.MYTABLE AS B
on a.mbrid = b.mbrid and a.key1 = b.key1 and a.startdate = b.startdate and
a.enddate = b.enddate AND A.STATUS <> B.STATUS
WHEN NOT MATCHED THEN
UPDATE SET A.STATUS = B.STATUS
ELSE IGNORE;

The idea above is that table B must be different from table A to work
correctly. Try this code with the idea you've got in mind, and should work.

HTH
JS

El mié, 24 jul 2024 a las 12:44, K Crawford (<kscx3ksc@xxxxxxxxx>) escribió:

I have a table that has an employee dependent relationship.
Table columns:
MbrID
key1
isDependent
StartDate
EndDate
Status
Name

Example Data:
123 blue N 01/01/2023 12/31/2023 E Sam Smith
123 blue N 01/01/2024 12/31/2024 X Sam Smith
123 blue Y 01/01/2023 12/31/2023 Olivia Smith
123 blue Y 01/01/2024 12/31/2024 Olivia Smith
789 blue N 01/01/2023 12/31/2023 E Ken Jones
789 red N 01/01/2024 12/31/2024 X Ken Jones
789 blue Y 01/01/2023 12/31/2023 Bob Jones
789 red Y 01/01/2024 12/31/2024 Bob Jones

I need to update the dependents with the related employee status. end
result looking like.
Example Date after update:
123 blue N 01/01/2023 12/31/2023 E Sam Smith
123 blue N 01/01/2024 12/31/2024 X Sam Smith
123 blue Y 01/01/2023 12/31/2023 E Olivia Smith
123 blue Y 01/01/2024 12/31/2024 X Olivia Smith
789 blue N 01/01/2023 12/31/2023 E Ken Jones
789 red N 01/01/2024 12/31/2024 X Ken Jones
789 blue Y 01/01/2023 12/31/2023 E Bob Jones
789 red Y 01/01/2024 12/31/2024 X Bob Jones

I have an sql that looks like it will join the rows together.
select a.mbrid, b.mbrid, a.key1, b.key1, a.isdependent, b.isdependent,
a.status, b.status, a.startdate, b.startdate, a.enddate, b.enddate,
a.name, b.name
from myLib.myTable a
*left* join myLib.myTable b
on a.mbrid = b.mbrid and a.key1 = b.key1 and a.startdate = b.startdate and
a.enddate = b.enddate

Can I get it to do the update to the dependent status column in the same
SQL?
Do I need to do some sort of CTE?

--
Kerwin Crawford
--
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.