UPSERT Within the Same Table

I have the following two tables: ALLOWANCE and RANGES. ALLOWANCE has the key column AllowanceID and corresponding to each AllowanceID, there will be a number of RANGE entries. Range has a primary key called RangeID and a reference to AllowanceID. Besides these, it has the following columns.

RANGESTART
RANGEEND
POUNDS
SUM

Now, I want to write a query to match the ranges of two AllowanceIDs. ie, Given SorceAllowanceID and TargetAllowanceID, I want to match the number of ranges as well as the range values of the souce to target. So I have the following query (I am using only one column here) followed by an INSERT statement(not shown here),

UPDATE    
( 
   SELECT 
          src.pounds AS src_pounds ,      
          tgt.pounds AS tgt_pounds     
   FROM     
       (
          SELECT  rank() OVER (PARTITION BY PR.ALLOWANCEID ORDER BY PR.RANGESTART)  RN
                  ,PR.* 
         FROM RANGES PR 
         WHERE PR.ALLOWANCEID=sourceallowanceid 
         ORDER BY PR.RANGESTART) src ,
        (SELECT  rank() OVER (PARTITION BY PR.ALLOWANCEID ORDER BY PR.RANGESTART)  PN
           ,PR.* 
         FROM ranges PR 
         WHERE PR.aLLOWANCEI开发者_StackOverflowD=targetallowanceid 
        ORDER BY PR.RANGESTART) TGT
    WHERE  src.Rn=tgt.PN
)
SET tgt_pounds = src_pounds;

But this has thrown ORA-01779. Then I tried with the MERGE statement

MERGE INTO 
  (   
  SELECT  rank() OVER (PARTITION BY PR.ALLOWANCEID ORDER BY PR.RANGESTART)  PN
       ,PR.* 
   FROM     RANGES PR 
   WHERE PR.ALLOWANCEID=targetallowanceid 
   ORDER BY PR.RANGESTART) tgt    
USING (SELECT  rank() OVER (PARTITION BY PR.ALLOWANCEID ORDER BY PR.RANGESTART)  RN
     ,PR.* 
   FROM RANGES PR 
   WHERE PR.ALLOWANCEID=sourceallowanceid 
  ORDER BY PR.RANGESTART) src   
 ON  ( src.RN = tgt.PN )
 WHEN MATCHED
    THEN
       UPDATE
       SET   tgt.pounds = src.pounds
WHEN NOT MATCHED THEN
    --Insert

which has thrown ORA-00903.

Please guide me how to write a query for this.

Thanking you all,

Pradeep

I don't believe you can merge into a sub-query so that's why you get ORA-00903: invalid table name. You can change that sub-query into a view, however, and it should work. Try this, for example:

CREATE VIEW sub-query subqview as
   SELECT  rank() OVER (PARTITION BY PR.ALLOWANCEID ORDER BY PR.RANGESTART)  PN
       ,PR.* 
   FROM     RANGES PR 
   WHERE PR.ALLOWANCEID=targetallowanceid 
   ORDER BY PR.RANGESTART;

MERGE INTO subqview tgt    
USING (SELECT  rank() OVER (PARTITION BY PR.ALLOWANCEID ORDER BY PR.RANGESTART)  RN
     ,PR.* 
   FROM RANGES PR 
   WHERE PR.ALLOWANCEID=sourceallowanceid 
  ORDER BY PR.RANGESTART) src   
 ON  ( src.RN = tgt.PN )
 WHEN MATCHED
    THEN
       UPDATE
       SET   tgt.pounds = src.pounds
WHEN NOT MATCHED THEN
    --Insert