title: Oracle Merge into ... When not matched insert ... - Can i use Alias
body: <p>Here a simplified table example: </p>
  
  <pre><code>MAGIC_TABLE
  titi | tata
  -----------
  val1 | magic1
  val2 | magic2
  val3 | magic1
  val4 | magic1
  val5 | magic2
  </code></pre>
  
  <p>What I want to achieve is that -> </p>
  
  <p>For every row that have <code>tata = 'magic2'</code> and some value in <code>titi</code> if it does not exist a <code>'magic1'</code> row with <strong>the same titi value</strong> then I should add a row with <code>titi.value</code> and <code>'magic1'</code></p>
  
  <p>Here is a simplified version of my SQL oracle query that I am working on : </p>
  
  <pre><code>merge into MAGIC_TABLE magic
  using (
      with UNMAGIC_TABLE as (
          select titi, tata from MAGIC_TABLE
          and tata='magic1'
      )
      SELECT titi, tata from UNMAGIC_TABLE 
  ) really_unmagic
  on 
  (
    magic.titi = really_unmagic.titi
    magic.tata = really_unmagic.tata
    and magic.tata='magic2'
  ) 
  when not matched then insert (titi, tata) 
  values 
  (magic.titi, 'magic1');
  </code></pre>
  
  <p>Somehow I keep getting this error : </p>
  
  <p><strong>ORA-38101: Invalid column in the INSERT VALUES Clause: "MAGIC"."TITI"</strong></p>
  
  <p>So I was wondering if it is a syntax issue around aliases? What did I do wrong there ?</p>
  
  <p><strong>EDIT</strong> : It's an inattention error, as  George Joseph  pointed out I have to use <code>unreally_magic</code> to make it work, since when it's not matched I do not have lines from <code>magic</code></p>