在9i中使用MERGE [英] using MERGE in 9i
本文介绍了在9i中使用MERGE的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想在9i中做这样的事情
I want to do something like this in 9i
MERGE INTO SAT_DEMANDS D
USING (SELECT DISTINCT d1.PRODUCT_ID AS product_id, CA.CUST_ID as allocated_cust
FROM CDP_MPS_ALLOCATION CA, sat_demands d1
WHERE substr(d1.product_id,1,instr(d1.product_id,'_VIRTUAL',1)-1) = ca.prod_id(+)) MC
ON (D.PRODUCT_ID = MC.product_id AND D.CUSTOMER_ID = MC.allocated_cust)
WHEN matched then
Update set D.alloc_cust_id_s = CASE WHEN mc.allocated_cust IS NULL THEN 'FREE'
ELSE mc.allocated_cust||':'||'FREE'
END;
请帮助! Oracle 9i不允许我这样做,因为在不匹配时它没有.我如何在9i中获得相同的结果?
Please help! Oracle 9i does not allow me to do this since it does not have when not matched. how do I achieve the same result in 9i?
推荐答案
使用rowid方法:
FOR r in (SELECT D.rowid, MC.allocated_cust FROM
(SELECT DISTINCT d1.PRODUCT_ID AS product_id, CA.CUST_ID as allocated_cust
FROM CDP_MPS_ALLOCATION CA, sat_demands d1
WHERE substr(d1.product_id,1,instr(d1.product_id,'_VIRTUAL',1)-1) = ca.prod_id(+)
) MC
join SAT_DEMANDS D
ON (D.PRODUCT_ID = MC.product_id AND D.CUSTOMER_ID = MC.allocated_cust)
)
LOOP
Update SAT_DEMANDS D
set D.alloc_cust_id_s = CASE WHEN r.allocated_cust IS NULL THEN 'FREE'
ELSE r.allocated_cust||':'||'FREE'
END
where rowid=r.rowid;
END LOOP;
这篇关于在9i中使用MERGE的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文