Help - Update using coalesce

[tdk425]

DB2 Gurus:

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 =
(SELECT
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);

 

[rbod]

When you do a
UPDATE table SET condition;
[without a where clause] you are updating the whole table.

So, add a WHERE clause to your query, to restrict the update to only matching rows.

UPDATE DVLT05.UPD_ACT_CNT U
SET ACT_CNT =
(SELECT M.ACT_CNT - 1)
FROM DVLT05.MIN_ACT_CNT M
WHERE M.VSAM_POL = U.VSAM_POL AND
M.TERM_SEQ = U.TERM_SEQ)
WHERE U.VSAM_POL IN (select M.VSAM_POL from M);