Merge语句结合了update与insert:
语法:
MERGE [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;
这里如果匹配ON之后的条件,则执行update
否则执行insert操作
详细可以看http://blog.csdn.net/suncrafted/article/details/4306677
在merge Into 的 using中使用分组查询条件:
MERGE INTO ZW_GJR a
USING (select b.three_grid_id,sum(c.amount)AS HJ
from Ftth_zy_grid b
left join Ftth_yj c
on c.four_grid_id = b.four_grid_id
group by b.three_grid_id) g
ON (a.wg = g.three_grid_id)
WHEN MATCHED THEN
UPDATE
SET a.cjzc =g.HJ;
在merge into 中的on后面使用条件限制:
>现在有表tbl_a,tbl_b:
>现在使用merge into插入数据,这里注意在on中做匹配的项不能改变:
>现在限制参与匹配更新的tbl_b表:
>查看更新后的a表: