Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005
    Posts
    2

    Angry Unanswered: Help - Update using coalesce

    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);

  2. #2
    Join Date
    Aug 2004
    Posts
    330
    I am not sure if you have the experession inside of the coalesce correct. Have you tested to be sure that NULL - 1 = NULL?

  3. #3
    Join Date
    Oct 2005
    Posts
    2
    Not sure what you mean.

    The clause M.ACT_CNT - 1 is working as expected.

    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.

  4. #4
    Join Date
    Aug 2004
    Posts
    330
    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.
    Last edited by urquel; 10-04-05 at 17:31.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •