title: Oracle sql merge to insert and delete but not update
body: <p>Is there a way to use oracle merge to insert and delete but not update?</p>
  
  <p>I have a table representing a set of values related to a single row in another table.  I could change the set of values by deleting them all and adding back the new set, or by selectively deleting some and adding others, but I am interested in making it a single statement if possible.</p>
  
  <p>Here is a working example with update.  In order to make this work, I had to add <code>dummy</code> so that a column was available to update that was not in the <code>on</code> condition.  Is there some way to only delete and insert without a dummy column to update?</p>
  
  <p>No column from the <code>on</code> condition may be in the <code>update set</code> list even if it is not actually updated.</p>
  
  <pre><code>create table every_value ( the_value varchar2(32) );
  create table paired_value ( the_id number, a_value varchar2(32) , dummy number default 0 );
  -- the_id is a foreign_key to a row in another table
  
  insert into every_value ( the_value ) values ( 'aaa' );
  insert into every_value ( the_value ) values ( 'abc' );
  insert into every_value ( the_value ) values ( 'ace' );
  insert into every_value ( the_value ) values ( 'adg' );
  insert into every_value ( the_value ) values ( 'aei' );
  insert into every_value ( the_value ) values ( 'afk' );
  
  -- pair ace and afk with id 3
  merge into paired_value p using every_value e
  on ( p.the_id = 3 and p.a_value = e.the_value )
  when matched then update set dummy=dummy+1
  delete where a_value not in ('ace','afk')
  when not matched then insert (the_id,a_value)
  values (3,e.the_value)
  where e.the_value in ('ace','afk');
  
  -- pair ace and aei with id 3
  -- should remove afk, add aei, do nothing with ace
  merge into paired_value p using every_value e
  on ( p.the_id = 3 and p.a_value = e.the_value )
  when matched then update set dummy = dummy+1
  delete where a_value not in ('ace','aei')
  when not matched then insert (the_id,a_value)
  values (3,e.the_value)
  where e.the_value in ('ace','aei');
  
  -- pair aaa and adg with id 4
  merge into paired_value p using every_value e
  on ( p.the_id = 4 and p.a_value = e.the_value )
  when matched then update set dummy = dummy+1
  delete where a_value not in ('aaa','adg')
  when not matched then insert (the_id,a_value)
  values (4,e.the_value)
  where e.the_value in ('aaa','adg');
  
  select * from paired_value;
  </code></pre>
  
  <p>I have tried this in oracle 10g and, with this <a href="http://sqlfiddle.com/#!4/a25ac/1">sqlfiddle</a>, oracle 11g.</p>