在开发时,如果遇到表需要加字段,那么需要对存量数据刷新这个字段值
1、mysql
--两张表关联
UPDATE JC_COLL_REPAY r
INNER JOIN lc_lm_loan l ON r.loan_no = l.loan_no
SET r.loan_typ = l.tep_cde,
r.oper_center = l.bch_cde,
r.chnl_cde = l.coopr_cde;
--三张表关联
UPDATE JC_COLL_REPAY r
INNER JOIN lc_lm_loan l ON r.loan_no = l.loan_no
INNER JOIN lc_cust_info i ON l.cust_id = i.cust_id
SET r.loan_typ = l.tep_cde,
r.oper_center = l.bch_cde,
r.chnl_cde = l.coopr_cde,
r.indiv_mobile = i.indiv_mobile;
2、oracle
---三张表关联
update JC_COLL_REPAY r
set (r.indiv_mobile,r.loan_typ,r.oper_center,r.chnl_cde) =
(select c.indiv_mobile,l.tep_cde,l.bch_cde,l.coopr_cde
from lc_lm_loan l,lc_cust_info c
where l.cust_id = c.cust_id and l.loan_no = r.loan_no
)
where exists (select 1
from lc_lm_loan l,lc_cust_info c
where l.cust_id = c.cust_id and l.loan_no = r.loan_no
)
---不推荐
update JC_COLL_REPAY r
set r.loan_typ=(select tep_cde from lc_lm_loan l where r.loan_no=l.loan_no),
r.oper_center=(select bch_cde from lc_lm_loan l where r.loan_no=l.loan_no),
r.chnl_cde=(select coopr_cde from lc_lm_loan l where r.loan_no=l.loan_no)
where exists (select 1 from a where r.loan_no=l.loan_no)
还可以使用merge into
merge into TEST_RESULT r
using (select * from test_major m,test_subject s where m.id = s.sb_ma) ms
on (r.subject = ms.sb_name)
when matched then
update set r.major = ms.zymc