I am trying to update a column in table A using a value derived from a column in table B. What I am trying to accomplish is to leave the original value in the table A column unchanged if there is no match found in table B. I have not been able to get this to work. The updates work OK when a match is found but when a match is not found the column in table A is NULLed. Is there a relatively easy way to accomplish this other than "where not exists"?
My non-working SQL looks like this:
UPDATE DVLT05.UPD_ACT_CNT U
SET ACT_CNT =
COALESCE (M.ACT_CNT - 1, U.ACT_CNT)
FROM DVLT05.MIN_ACT_CNT M
WHERE M.VSAM_POL = U.VSAM_POL
AND M.TERM_SEQ = U.TERM_SEQ);
When there is no row found in table M (MIN_ACT_CNT) I would like for it to plug in or retain the original value in table U (UPD_ACT_CNT) column ACT_CNT. It does not. It NULLs the column so the original value is lost.
It sounds like you need something besides the COALESCE function. It evaluates a column value in an existing row to see whether the value is NULL and replaces NULL values with the second argument of the function. It doesn't do anything if there is no ROW. If you want to guarantee a row in your subselect, consider doing an outer join with the sysdummy1 table on 1=1.