title: ORA-30926 - Merge state
body: <p>I am getting an ORA-30926 error. I researched this and found that this is usually caused by duplicates in the query specified in the<code>USING</code> clause.</p>
  
  <p>The problem is I am intially removing duplicates and storing in a temperory table (<code>temp_distinct</code>) which in turn I am referring to in the <code>MERGE</code>. Here is my code snippet:</p>
  
  <pre><code>MERGE INTO name_test nt 
  USING (select name from temp_distinct) s 
  ON (1=1)
  WHEN MATCHED
  THEN UPDATE SET nt.fn = s.name, nt.LN = s.name
  </code></pre>
  
  <p>Here is the structure of my tables:</p>
  
  <p><code>NAME_TEST</code>:</p>
  
  <pre><code>FN    LN
  ----- -----
  Ruc   Rag
  Ruc   Ran
  Sam   Kum
  Ruc   Ran
  Ruc   Kum
  Ran   Dev
  Rag   Agar
  Rag   Ran
  </code></pre>
  
  <p><code>TEMP_DISTINCT</code>:</p>
  
  <pre><code>FN    NUMB NAME  NUM
  ----- ---- ----- ---
  Sam   1    Mark  1
  Rag   2    Steve 2
  Dev   3    John  3
  Kum   4    Dave  4
  Ruc   5    Mich  5
  Agar  6    Dean  6
  Ran   7    Phil  7
  </code></pre>
  
  <p>So as you can see there is no duplicate in <code>USING</code> clause. I am trying to replace <code>NT.FN = S.NAME</code> and also <code>NT.LN = S.NAME</code>.</p>
  
  <p>Basically I want to replace names in <code>FN</code> and <code>LN</code> from <code>NAME_TEST</code> table with different name from <code>TEMP_DISTINCT</code> table. Final output should be like below:</p>
  
  <pre><code>FN     LN
  ------ ------
  Mich   Steve
  Mich   Phil
  Mark   Dave
  Mich   Phil
  Mich   Dave
  Phil   John
  Steve  Dean
  Steve  Ran
  </code></pre>