title: Oracle: Is there a simple way to say "if null keep the current value" in merge/update statements?
body: <p>I have a rather weak understanding of any of oracle's more advanced functionality but this should I think be possible.</p>
  
  <p>Say I have a table with the following schema:</p>
  
  <pre><code>MyTable
    Id INTEGER,
    Col1 VARCHAR2(100),
    Col2 VARCHAR2(100)
  </code></pre>
  
  <p>I would like to write an sproc with the following </p>
  
  <pre><code>PROCEDURE InsertOrUpdateMyTable(p_id in integer, p_col1 in varcahr2, p_col2 in varchar2)
  </code></pre>
  
  <p>Which, in the case of an update will, if the value in p_col1, p_col2 is null will not overwrite Col1, Col2 respectively</p>
  
  <p>So If I have a record:</p>
  
  <pre><code>id=123, Col1='ABC', Col2='DEF'
  
  exec InsertOrUpdateMyTable(123, 'XYZ', '098'); --results in id=123, Col1='XYZ', Col2='098'
  exec InsertOrUpdateMyTable(123, NULL, '098');  --results in id=123, Col1='ABC', Col2='098'
  exec InsertOrUpdateMyTable(123, NULL, NULL);   --results in id=123, Col1='ABC', Col2='DEF'
  </code></pre>
  
  <p>Is there any simple way of doing this without having multiple SQL statements?  </p>
  
  <p>I am thinking there might be a way to do this with the Merge statement though I am only mildly familiar with it.</p>
  
  <hr>
  
  <p><strong>EDIT:</strong>
  Cade Roux bellow suggests using COALESCE which works great!  <a href="http://www.java2s.com/Code/Oracle/Conversion-Functions/COALESCEreturnsthefirstnonnullexpressionintheexpressionlist.htm" rel="nofollow noreferrer">Here are some examples of using the coalesce kewyord.</a>
  And here is the solution for my problem:</p>
  
  <pre><code>MERGE INTO MyTable mt
      USING (SELECT 1 FROM   DUAL) a
      ON (mt.ID = p_id)
      WHEN MATCHED THEN
          UPDATE
             SET mt.Col1 = coalesce(p_col1, mt.Col1), mt.Col2 = coalesce(p_col2, mt.Col2)
      WHEN NOT MATCHED THEN
          INSERT (ID, Col1, Col2)
          VALUES (p_id, p_col1, p_col2);
  </code></pre>