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


i working @ company oracle 9i 9.2, , unable upgrade.

a merge update on matched, not insert on not-matched, seems not work in version.

i trying do:

merge cdlrefwork.pricing d --table insert     using    v_rec  s  --table source     on ( d.item_id = s.item_id         ,   d.line_type = s.line_type         , d.price_code =s.price_code )    when matched 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 isn't possible in 9i, how equivalent update instead?

the syntax diagram 9i shows had have both when matched , when not matched clauses. that changed in 10gr1 (and mentioned in new features list); doesn't if can't upgrade - explains why doesn't work. trying update 2 of 3 columns join clause, isn't allowed.

you can 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)   v_rec s   s.item_id =d.item_id   , s.line_type = d.line_type   , s.price_code = d.price_code ) exists (   select null   v_rec s   s.item_id =d.item_id   , s.line_type = d.line_type   , s.price_code = d.price_code ); 

i've taken out item_id , line_type columns know match. where exists clause means rows in pricing have matching row in v_rec updated. may mean nvl() calls redundant, , need select value s, without knowing data it's hard sure.


Comments

Popular posts from this blog

account - Script error login visual studio DefaultLogin_PCore.js -

xcode - CocoaPod Storyboard error: -