背景
有个业务需要跨表批量更新。需要将B表中的批复金额更新到A表中,之前都做外关联查询。发现merge能更简单更清晰的实现。
语法
MERGE [hint] INTO [schema ] table [t_alias]
USING [schema ]{ table | view | subquery } [t_alias]
ON ( condition )
WHEN MATCHED THEN merge_update_clause
WHEN NOT MATCHED THEN merge_insert_clause;
语句展示
merge into zjgrbbmx a
using (select b.zjbmbb_grbbmxid,
b.zjbmbb_finalamount,
b.zjbmbb_finalpjamount
from zjbmbb b
where b.zjbmbb_djid = 'f1661080-d614-4747-b2f5-c0bd8e2d09e1') t
on (t.zjbmbb_grbbmxid = a.zjbmbb_guid)
when matched then
update
set a.zjbmbb_finalamount = t.zjbmbb_finalamount,
a.zjbmbb_finalpjamount = t.zjbmbb_finalpjamount;
后记
整体是这个用法,接触尚浅,如有错误,欢迎指出。