求一条连接更新语句

[qq707472]

select * from lm_loan ln where ln.bch_cde in （‘1’，‘2’，’3‘） and ln.loan_deva_ind='Y' and ln.loan_sts='ACTV'

update lm_ln_info li set li.Ln_Cost_Prod =Ln_Cost_Prod+(lm_loan.loan_os_prcp-TOT_PROV_AMT) where li.loan_no = 上面查出


这个语句本来应该是查询第一条sql的之后得出的多条 lmloan 记录，之后遍历lmloan 记录来执行第二条更新语句，但是现在需要改成用一条连接更新语句 对表进行更新。希望大家帮忙!

[SQL77]

 

update lm_ln_info li set li.Ln_Cost_Prod =Ln_Cost_Prod+(lm_loan.loan_os_prcp-TOT_PROV_AMT) where li.loan_no IN (Select loan_no from lm_loan ln where ln.bch_cde in （‘1’，‘2’，’3‘） and ln.loan_deva_ind='Y'  and ln.loan_sts='ACTV')

[amiksong]

update lm_ln_info li set li.Ln_Cost_Prod =Ln_Cost_Prod+(lm_loan.loan_os_prcp-TOT_PROV_AMT) where exists(select * from lm_loan ln where ln.bch_cde in （‘1’，‘2’，’3‘） and ln.loan_deva_ind='Y'  and ln.loan_sts='ACTV' and ln.loan_no=li.loan_no);

[BlueskyWide]

1.如果楼主数据很重要的话，不妨做一个数据备份，否则原数据会破坏。
2.先动手自已做两个表，试着在两表之间使用update语句。参考一下：http://blog.sina.com.cn/s/blog_511041a10100ah2b.html
3.应给出lm_loan,lm_ln_info两表的详细字段，以作参考之用。

[zhangwonderful]

建议使用exists性能较in好。
update lm_ln_info li set li.Ln_Cost_Prod =Ln_Cost_Prod+(lm_loan.loan_os_prcp-TOT_PROV_AMT) where exists (Select loan_no from lm_loan ln where ln.bch_cde in （‘1’，‘2’，’3‘） and ln.loan_deva_ind='Y' and ln.loan_sts='ACTV' and li.loan_no=ln.loan_no)

[碧水幽幽泉]

jf 同意楼上

[qq707472]

请大家注意了 lm_loan.loan_os_prcp 也是从第一条sql语句中查询出来的

[小灰狼W]

[Quote=引用 10 楼 qq707472 的回复:]
请大家注意了  lm_loan.loan_os_prcp  也是从第一条sql语句中查询出来的
[/Quote]看看6楼的代码

[inthirties]

update lm_ln_info li set li.Ln_Cost_Prod = (select Ln_Cost_Prod +lm_loan.loan_os_prcp from lm_loan where ln.bch_cde in （‘1’，‘2’，’3‘） and ln.loan_deva_ind='Y' and ln.loan_sts='ACTV' and li. loan_no = lm_loan. loan_no)

[inthirties]

update lm_ln_info li set li.Ln_Cost_Prod = (select Ln_Cost_Prod +lm_loan.loan_os_prcp from lm_loan ln where ln.bch_cde in （‘1’，‘2’，’3‘） and ln.loan_deva_ind='Y' and ln.loan_sts='ACTV' and li. loan_no = lm_loan. loan_no and rownum = 1) where li.loan_no exists (select 1 from (select * from lm_loan ln where ln.bch_cde in （‘1’，‘2’，’3‘） and ln.loan_deva_ind='Y' and ln.loan_sts='ACTV'))

上面的一句忽略，被把没有符合的也更新为null了。用下面一句。

[mark620]

最好采用游标来做。

[qq707472]

6楼的OK

[小灰狼W]

update lm_ln_info li set ln_cost_prod=
  (select li.ln_cost_prod+ln.loan_os_prcp-ln.tot_prov_amt from lm_loan ln
    where ln.bch_cde in('1','2','3') and ln.loan_deva_ind='Y' and ln.loan_sts='ACTV'
    and li.loan_no=ln.loan_no)
where exists
    (select 1 from lm_loan where bch_cde in('1','2','3') and loan_deva_ind='Y' 
      and loan_sts='ACTV' and li.loan_no=loan_no)

[cosio]

update lm_ln_info li set li.Ln_Cost_Prod =Ln_Cost_Prod+(lm_loan.loan_os_prcp-TOT_PROV_AMT) where exists (Select loan_no from lm_loan ln where ln.bch_cde in （‘1’，‘2’，’3‘） and ln.loan_deva_ind='Y' and ln.loan_sts='ACTV' and li.loan_no=ln.loan_no) 

[sjm5210]

同意7楼的答案

[thesecretblue]

update lm_ln_info li set li.Ln_Cost_Prod =Ln_Cost_Prod+(lm_loan.loan_os_prcp-TOT_PROV_AMT) where exists (Select loan_no from lm_loan ln where ln.bch_cde in （‘1’，‘2’，’3‘） and ln.loan_deva_ind='Y' and ln.loan_sts='ACTV' and li.loan_no=ln.loan_no)