insert successful but update failure

[yimwai]

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

[chulett]

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?

[yimwai]

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

[chulett]

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.