When updating multiple columns, prevent updating null in case of no row is returned from select

Question

I am working on db2 z/os 10.

when I am updating a single column I can write query like this (in order to prevent null in column A.COL1).

---

UPDATE TABLE1 A
SET A.COL1 = COALESCE((
            SELECT B.COL1
            FROM TABLE2 B
            WHERE A.KEY = B.KEY
            ), '');

---

However in case of updating multiple columns since I can not use COALESCE, I have to use "Exists" clause (like below).

---

UPDATE TABLE1 A
SET (
        A.COL1
        ,A.COL2
        ) = (
        SELECT B.COL1
            ,B.COL2
        FROM TABLE2 B
        WHERE A.KEY = B.KEY
        )
WHERE EXISTS (
        SELECT 'X'
        FROM TABLE2 B
        WHERE A.KEY = B.KEY
        );

---

Can I re-write second query using only scalar function (without EXISTS) and prevent updating null in COL1 and COL2.

Answer 1

I think the following will work (I don't have DB2 on hand to test it):

UPDATE TABLE1 A
    SET (A.COL1, A.COL2) =
        (SELECT COALESCE(MAX(B.COL1), A.COL1),
                COALESCE(MAX(B.COL2), A.COL2)
         FROM TABLE2 B
         WHERE A.KEY = B.KEY
        );

The MAX() guarantees that exactly one row is returned -- even when there are no matches. The rest just chooses which value to use. Do note that this will keep the current value, even when the matching value is NULL (that is, there is a match but the value in the row is NULL).

Also, the EXISTS in the WHERE clause is typically a good idea, because it reduces the number of rows that need to be accessed for the update.