多表关联更新的案例及总结

1. 关联更新需找出表的相关唯一键，同时唯一键也可能在关联的同时对应多个值，主表的更新的条件需要与子查询的条件一致，否则会出现多值的情况，当出现多值或重复值时，需要优先处理

update tab_base_moneylist mm set mm.money=(select ttemp.price*0.98/100 from
(select m.id,m.money,c.price from tab_base_moneylist m,tab_topup_moneylist n,tab_topup_mobile_cardpay c
where n.pay_id=c.id
and m.id=n.moneylist_id
and m.createdate>=to_date('2012-06-10','yyyy-mm-dd')
and m.createdate<=to_date('2012-06-13 09:30:00','yyyy-mm-dd hh24:mi:ss')
and c.supply_id=2926) ttemp where mm.id=ttemp.id) where exists
(
select 1 from
(select m.id,m.money,c.price from tab_base_moneylist m,tab_topup_moneylist n,tab_topup_mobile_cardpay c
where n.pay_id=c.id
and m.id=n.moneylist_id
and m.createdate>=to_date('2012-06-10','yyyy-mm-dd')
and m.createdate<=to_date('2012-06-13 09:30:00','yyyy-mm-dd hh24:mi:ss')
and c.supply_id=2926) ttemp where mm.id=ttemp.id
);
commit;

update tab_topup_auditorderitem aoi1 set aoi1.agent_id=(
select ttemp.oi_agent_id from
(select aoi.id,aoi.agent_id aoi_agent_id,oi.agent_id oi_agent_id,aoi.refundmoney,oi.parvalue*oi.money pm from tab_topup_auditorderitem aoi,tab_topup_orderitem oi
where aoi.item_id=oi.id
and aoi.createdate>to_date('2012-07-10','yyyy-MM-dd')
and aoi.refundmoney>0) ttemp where aoi1.id=ttemp.id),aoi1.refundprice=(
select ttemp.pm
from
(select aoi.id,aoi.agent_id aoi_agent_id,oi.agent_id oi_agent_id,aoi.refundmoney,aoi.refundmoney/oi.parvalue*oi.money pm from tab_topup_auditorderitem aoi,tab_topup_orderitem oi
where aoi.item_id=oi.id
and aoi.createdate>to_date('2012-07-10','yyyy-MM-dd')
and aoi.refundmoney>0) ttemp where aoi1.id=ttemp.id),aoi1.statdate=trunc(aoi1.auditdate)
 where exists
(
select 1 from
(select aoi.id,aoi.agent_id aoi_agent_id,oi.agent_id oi_agent_id,aoi.refundmoney,oi.parvalue*oi.money pm from tab_topup_auditorderitem aoi,tab_topup_orderitem oi
where aoi.item_id=oi.id
and aoi.createdate>to_date('2012-07-10','yyyy-MM-dd')
and aoi.refundmoney>0) ttemp where aoi1.id=ttemp.id
);

commit;

select * from tab_topup_auditorderitem aoi1 where AOI1.STATDATE is not null

 

/* Formatted on 2012/7/27 17:25:17 (QP5 v5.115.810.9015) */
UPDATE   tab_topup_borrow_business bb  SET   bb.projectid = (
SELECT   ab.id  FROM
(SELECT   a.id, b.id bid  FROM   tab_topup_borrow_project a,tab_topup_borrow_business b
                        WHERE   b.projectid = a.code) ab where  bb.id=ab.bid)
 WHERE   EXISTS
            (SELECT   1
               FROM   (SELECT   a.id, b.id bid
                         FROM   tab_topup_borrow_project a,
                                tab_topup_borrow_business b
                        WHERE   b.projectid = a.code) ab where bb.id=ab.bid);

 

以上SQL 有重复值，需要去除重复值再执行关联更新

delete from tab_topup_borrow_business where id in(                       
select bid from bus_test group by bid having count(*)>1
);
commit;

/* Formatted on 2012/7/30 13:50:22 (QP5 v5.115.810.9015) */
UPDATE   tab_topup_borrow_business bb
   SET   bb.projectid =
            (SELECT   ttemp.aid
               FROM   (SELECT   a.id aid, b.id bid, b.projectid
                         FROM   tab_topup_borrow_project a,
                                tab_topup_borrow_business b
                        WHERE   b.projectid = TO_NUMBER (a.code)) ttemp
              WHERE   bb.id = ttemp.bid)
 WHERE   EXISTS
            (SELECT   1
               FROM   (SELECT   a.id, b.id bid, b.projectid
                         FROM   tab_topup_borrow_project a,
                                tab_topup_borrow_business b
                        WHERE   b.projectid = TO_NUMBER (a.code)) ttemp
              WHERE   bb.id = ttemp.bid);

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25198367/viewspace-740476/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25198367/viewspace-740476/