Re: SQL update column join on same table

Then WHEN NOT MATCHED clause is wrong, sorry for that, it should be WHEN
MATCHED, since the STATUS field has to be different to get a MATCH.



El mié, 24 jul 2024 a las 20:06, Javier Sanchez (<
javiersanchezbarquero@xxxxxxxxx>) escribió:

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.