Db2 Developer-C 11.1

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

--create table_1
CREATE TABLE MD001 (MRN, Name, Address) AS 
(
VALUES 
  (1, 'NameName1', 'Add1')
, (2, 'NameName2', 'Add2')
, (3, 'NameName3', 'Add3')
) WITH DATA
CREATE TABLE PATIENT (CaseNo, FName, MName, LName) AS 
(
VALUES 
  ('1', 'F', 'M', 'L')
, ('2', '', '', '')
) WITH DATA
WITH U AS (
    SELECT COUNT(1) AS dummy
    FROM NEW TABLE (
        UPDATE MD001 m
        SET NAME = (
            SELECT CASE
                WHEN FName IS NOT NULL AND MName IS NOT NULL AND LName IS NOT NULL
                THEN FName || ' ' || MName || ' ' || LName
                ELSE m.NAME
            END
            FROM PATIENT p
            --WHERE p.caseno = m.MRN
            WHERE fname <> '' AND mname <> '' AND lname <> ''
        )
        WHERE EXISTS (
            SELECT 1 FROM PATIENT p WHERE p.caseno = m.MRN
        )
    )
)
SELECT * FROM MD001;




MRN
NAME
ADDRESS


1
F M L
Add1

2
F M L
Add2

3
NameName3
Add3