merge into test_7_sp_st_2000_mm a
using (select *
from sp_dim_deduct_penalty
where department_id = 20
and op_time = to_date('20110901', 'yyyymmdd')) b
on (a.sp_id = b.sp_code and a.service_id = b.service_id and a.product_id = b.product_code)
when matched then
update
set wang_test_1108.penalty_charge = wang_test_1108.penalty_charge -
(a.all_penalty_charge -
b.penalty_charge)
where wang_test_1108.op_time = to_date('20110901', 'yyyymmdd')
and wang_test_1108.area_id = -99
and a.all_penalty_charge -b.penalty_charge>0
这是一个错误的语句;在两个----------------之间的是我想要做的
我相当表A和表B a.sp_id=b.sp_code and a.service_id=b.service_id and a.product_id=b.product_code相等时
判断表A的all_penalty_charge和表B的penalty_charge, 当表A的all_penalty_charge大于表B的penalty_charge时
对表wang_test_1108进行更新
能够实现吗?如果能怎么实现?
merge into 目标表
using (数据源)
on (匹配条件)
。。。。
so,你的wang_test_1108应该写在into后面,using里写a和b的关联加上B a.sp_id=b.sp_code and a.service_id=b.service_id and a.product_id=b.product_code and a.all_penalty_charge >b.penalty_charge
应该就可以了
注意一个被更新行只能对应一条数据源