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



Glad to hear that!

El jue, 25 jul 2024 a las 12:15, K Crawford (<kscx3ksc@xxxxxxxxx>) escribió:

The MERGE will work with the Source and Target being the same table. I
think it is working.
Kerwin

On Thu, Jul 25, 2024 at 7:43 AM K Crawford <kscx3ksc@xxxxxxxxx> wrote:

It is the same table.
I guess I could copy the table and make two of them.

On Wed, Jul 24, 2024 at 9:14 PM Javier Sanchez <
javiersanchezbarquero@xxxxxxxxx> wrote:

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.


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



--
Kerwin Crawford



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



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.