title: Oracle: Merge equivalent of insert all?
body: <p>I've tried to find an answer on several forums with no luck, so perhaps you can help me out.
  I've got an INSERT ALL request that inserts thousands of rows at once. </p>
  
  <pre><code>INSERT ALL
     INTO my_table (field_x, field_y, field_z) VALUES ('value_x1', 'value_y1', 'value_z1')
     INTO my_table (field_x, field_y, field_z) VALUES ('value_x2', 'value_y2', 'value_z2')
     ...
     INTO my_table (field_x, field_y, field_z) VALUES ('value_xn', 'value_yn', 'value_zn')
  SELECT * FROM DUAL;
  </code></pre>
  
  <p>Now I'd like to amend it to update rows when some criteria are met. For each row, I could have something like:</p>
  
  <pre><code>MERGE INTO my_table m
      USING (SELECT 'value_xi' x, 'value_yi' y, 'value_zi' z FROM DUAL) s
      ON (m.field_x = s.x and m.field_y = s.y)
      WHEN MATCHED THEN UPDATE SET 
          field_z = s.z,
      WHEN NOT MATCHED THE INSERT (field_x, field_y, field_z) 
          VALUE(s.x, s.y, s.z);
  </code></pre>
  
  <p>Is there a way for me to do a kind of "MERGE ALL" that would allow to have all those merge requests in one?</p>
  
  <p>Or maybe I'm missing the point and there's a better way to do this?</p>
  
  <p>Thanks,</p>
  
  <p>Edit: One possible solution is to use "UNION ALL" for a set of selects from dual, as follows:</p>
  
  <pre><code>MERGE INTO my_table m
      USING (
          select '' as x, '' as y, '' as z from dual
          union all select 'value_x1', 'value_y1', 'value_z1' from dual
          union all select 'value_x2', 'value_y2', 'value_z2' from dual
          [...]
          union all select 'value_xn', 'value_yn', 'value_zn' from dual
      ) s
      ON (m.field_x = s.x and m.field_y = s.y)
      WHEN MATCHED THEN UPDATE SET 
          field_z = s.z,
      WHEN NOT MATCHED THEN INSERT (field_x, field_y, field_z) 
          VALUES (s.x, s.y, s.z);
  </code></pre>
  
  <p>NB: I've used a first empty row to be able generate all rows in the same format when I write the request. I also specify the columns names there.</p>
  
  <p>Another solution would be to create a temporary table, INSERT ALL data into it, then merge with the target table and delete the temporary table.</p>