CODEWITHSUNDEEP
sqloracle-database
Web-GIS entrepreneur
Web-GIS entrepreneur

Reputation: 186

Alternatives for NOT using MERGE INTO in Oracle SQL

I was using the following with success:

    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;

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.

Here is my table and the results:

my data

Now i want to do the same but avoiding MERGE INTO, and I've tried the following with no success:

    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;

The latest SQL code is giving me this:

SQL Error: ORA-01732: data manipulation operation not legal on this view
01732. 00000 -  "data manipulation operation not legal on this view"

Any ideas?

Upvotes: 0

Views: 3432

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269943

How about:

update table_name t
    set col2 = col1 - coalesce( (select max(col1) keep (dense_rank first order by t2.id desc)
                                 from table_name t2
                                 where t2.type = t.type and t2.id < t.id
                                ), 0);

This is a different approach but it doesn't use merge.

Upvotes: 2

Related Questions