DSXchange

I user upsert mode user_defined update & insert
the insert statement is
INSERT INTO CC_CDW.M_CLT_REL (CLT_NBR, ALT_CLT_NBR, STR_DTE, END_DTE) VALUES (ORCHESTRATE.CLT_NBR, ORCHESTRATE.ALT_CLT_NBR, ORCHESTRATE.STR_DTE, ORCHESTRATE.END_DTE)

the update statement is
UPDATE CC_CDW.M_CLT_REL
SET
END_DTE=DATE('#I_DATE#')-1 DAY
WHERE (
CLT_NBR = ORCHESTRATE.CLT_NBR
AND
ALT_CLT_NBR = ORCHESTRATE.ALT_CLT_NBR AND STR_DTE<> ORCHESTRATE.STR_DTE)

job runs fine. But I find no record has been updated.
When I use user_defined update only and keep the statement the same
update can be done successfully

Your update will only fire if the insert fails and for that to happen you basically need to violate a unique constraint, i.e. insert a duplicate key. Do you have such a constraint on your table?

thanks, I thought the input data can be used totally twice in insert and update.

No, it's more Insert Else Update rather than Insert And Update from what I recall. Could be wrong as it has been quite some time since I've actually worked with the tool.