title: Alternatives for NOT using MERGE INTO in Oracle SQL
body: <p>I was using the following with success:</p>
  
  <pre><code>    MERGE INTO table_name tab1 
      USING (SELECT ID,(NVL(col1,0) - NVL(LAG(NVL(col1,0),1) OVER 
  (PARTITION BY type order by ID),0)) VAL FROM table_name) bb 
      ON (tab1.ID = bb.ID)
      WHEN matched THEN
        UPDATE SET tab1.col2 = bb.VAL;
  </code></pre>
  
  <p>which is basically an UPDATE on the same table by using operations on the same table.The sub-query returns MORE than one row, this is important.</p>
  
  <p>Here is my table and the results:</p>
  
  <p><a href="https://i.stack.imgur.com/ytvsy.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ytvsy.png" alt="my data"></a></p>
  
  <p>Now i want to do the same but avoiding MERGE INTO, and I've tried the following with no success:</p>
  
  <pre><code>    UPDATE 
      (SELECT aa.COL2 as OLD, (NVL(aa.COL1,0) - NVL(LAG(NVL(aa.COL1,0),1) 
  OVER (PARTITION BY aa.TYPE order by aa.ID),0)) AS NEW
       FROM table_name aa
       INNER JOIN table_name bb
       ON aa.ID = bb.ID
      ) t
      SET t.OLD = t.NEW;
  </code></pre>
  
  <p>The latest SQL code is giving me this:</p>
  
  <pre><code>SQL Error: ORA-01732: data manipulation operation not legal on this view
  01732. 00000 -  "data manipulation operation not legal on this view"
  </code></pre>
  
  <p>Any ideas?</p>