Merge with only 'when matched then update' In Oracle 9i

Question

I am working at a company with Oracle 9i 9.2, and I am unable to upgrade.

A merge which only does an update on matched, not an insert on not-matched, seems not to work in this version.

I am trying to do:

MERGE INTO CDLREFWORK.pricing d --table to insert to 
   USING    V_REC  S  --table source 
   ON ( D.item_id = S.item_id
        and   d.line_type = s.line_type
        AND d.price_code =s.price_code )
   WHEN MATCHED THEN UPDATE SET
    d.APPLICATION_ID='CPMASI',
    d.SYS_UPDATE_DATE=SYSDATE,
    d.OPERATOR_ID=nvl(s.OPERATOR_ID,  d.OPERATOR_ID),
    d.LOCATION_ID=nvl(s.LOCATION_ID,d.LOCATION_ID),
    d.ITEM_ID= nvl(s.ITEM_ID,d.ITEM_ID),
    d.LINE_TYPE= nvl(s. LINE_TYPE, d.LINE_TYPE),
    d.EXPIRATION_DATE=nvl(s.EXPIRATION_DATE,d.EXPIRATION_DATE),
    d.PRICE_CODE= nvl(s.PRICE_CODE,d.PRICE_CODE),
    d.TO_QTY=nvl(s.TO_QTY,d.TO_QTY),
    d.PRICE= nvl(s.PRICE,d.PRICE),
    d.CHARGE_CODE=nvl(s.CHARGE_CODE,d.CHARGE_CODE),
    d.SOC=nvl(s.SOC,d.SOC), 
    d.COMMITMENT=nvl(s.COMMITMENT,d.COMMITMENT), 
    d.CAMBIAZO_CODE=nvl(s.CAMBIAZO_CODE,d.CAMBIAZO_CODE),  
    d.PPG_IND=nvl(s.PPG_IND,d.PPG_IND);

This gets:

SQL Error: ORA-00905: missing keyword
00905. 00000 -  "missing keyword"

If this isn't possible in 9i, then how would I do an equivalent update instead?

Answer 1

The syntax diagram for 9i shows that you had to have both when matched and when not matched clauses. That changed in 10gR1 (and is mentioned in the new features list); but that doesn't really help you if you can't upgrade - it just explains why it doesn't work. You were also trying to update two of the three columns from the join clause, which isn't allowed.

You can do a correlated update instead:

UPDATE CDLREFWORK.pricing d
SET (d.APPLICATION_ID, d.SYS_UPDATE_DATE, d.OPERATOR_ID, d.LOCATION_ID,
  d.EXPIRATION_DATE, d.PRICE_CODE, d.TO_QTY, d.PRICE, d.CHARGE_CODE, d.SOC,
  d.COMMITMENT, d.CAMBIAZO_CODE, d.PPG_IND)
= (
  SELECT 'CPMASI',
    SYSDATE,
    nvl(s.OPERATOR_ID,  d.OPERATOR_ID),
    nvl(s.LOCATION_ID,d.LOCATION_ID),
    nvl(s.EXPIRATION_DATE,d.EXPIRATION_DATE),
    nvl(s.PRICE_CODE,d.PRICE_CODE),
    nvl(s.TO_QTY,d.TO_QTY),
    nvl(s.PRICE,d.PRICE),
    nvl(s.CHARGE_CODE,d.CHARGE_CODE),
    nvl(s.SOC,d.SOC), 
    nvl(s.COMMITMENT,d.COMMITMENT), 
    nvl(s.CAMBIAZO_CODE,d.CAMBIAZO_CODE),  
    nvl(s.PPG_IND,d.PPG_IND)
  FROM V_REC s
  WHERE s.item_id =d.item_id
  AND s.line_type = d.line_type
  AND s.price_code = d.price_code
)
WHERE EXISTS (
  SELECT null
  FROM V_REC s
  WHERE s.item_id =d.item_id
  AND s.line_type = d.line_type
  AND s.price_code = d.price_code
);

I've taken out the item_id and line_type columns as you already know they match. The where exists clause means only rows in pricing which actually have a matching row in v_rec are updated. That may mean the nvl() calls are redundant, and you just need to select the value from s, but without knowing your data it's hard to be sure.