Merge "WHEN MATCHED" with "AND"

In Oracle, you cannot use AND directly in the WHEN MATCHED clause. Instead, you attach a WHERE clause to the UPDATE action to conditionally execute it.

Your syntax is correct. Here is the proper structure:

MERGE INTO test t
USING (SELECT 1 AS col1, 'B' AS col2, 'C' AS col3, 'D' AS col4, 'X' AS col5 FROM dual) inp
ON (t.col1 = inp.col1 AND t.col2 = inp.col2)
WHEN MATCHED THEN
    UPDATE SET t.col3 = inp.col3,
               t.col4 = inp.col4,
               t.col5 = inp.col5
    WHERE t.col3  inp.col3  -- Condition for the update
WHEN NOT MATCHED THEN
    INSERT (col1, col2, col3, col4, col5)
    VALUES (inp.col1, inp.col2, inp.col3, inp.col4, inp.col5);

The WHERE t.col3 inp.col3 ensures the update only happens if that condition is met, preventing unnecessary updates. This is Oracle's standard syntax as documented in the Oracle MERGE statement documentation.