Merge Into 语法支持
KINGBASE 兼容Oracle 语法,实现了merge into 的功能。以下以例子的形式,介绍merge into语法的使用。以下例子在V8R6 ,且 database_mode=oracle 环境下验证过,database_mode=pg 不支持merge into 语法。
一、创建测试数据
create table source_table(s_id integer,s_name char(9)); create table target_table(t_id integer,t_name char(9)); insert into source_table values(1,'s_a'),(2,'s_b'); insert into target_table values(1,'t_a'),(3,'t_c'); test=# select * from source_table ; s_id | s_name ------+----------- 1 | s_a 2 | s_b (2 rows) test=# select * from target_table ; t_id | t_name ------+----------- 1 | t_a 3 | t_c (2 rows)
二、测试例子
1、例子1
test=# begin; BEGIN test=# merge into target_table as t test-# using source_table as s test-# on (t.t_id=s.s_id) test-# when matched then update set t.t_name=s.s_name test-# when not matched then insert values(s.s_id,s.s_name); MERGE 2 test=# select * from target_table order by t_id; t_id | t_name ------+----------- 1 | s_a 2 | s_b 3 | t_c (3 rows) test=# rollback; ROLLBACK
注意:更新的列不能是ON 条件中被引用的列。
2、例子2
更新时可以通过WHERE 条件指明要更新的行,条件中既可以包含源表的列,也可以包含目标表的列,当指明WHERE 条件且条件为假时,则不更新。
test=# begin; BEGIN test=# merge into target_table as t test-# using source_table as s test-# on (t.t_id=s.s_id) test-# when matched then update set t.t_name=s.s_name where t.t_id=3 test-# when not matched then insert values(s.s_id,s.s_name); MERGE 1 test=# test=# select * from target_table order by t_id; t_id | t_name ------+----------- 1 | t_a --没有被更新 2 | s_b 3 | t_c (3 rows) test=# rollback; ROLLBACK
3、例子3
test=# begin; BEGIN test=# merge into target_table as t test-# using source_table as s test-# on (t.t_id=s.s_id) test-# when matched then update set t.t_name=s.s_name test-# delete where t.t_name='s_a' test-# when not matched then insert values(s.s_id,s.s_name); MERGE 3 test=# select * from target_table order by t_id; t_id | t_name ------+----------- 2 | s_b 3 | t_c (2 rows) test=# rollback; ROLLBACK
DELETE 子句只删除目标表和源表的ON 条件为真、并且是更新后的符合删除条件的记录,DELETE 子句不影响INSERT 项插入的行
三、Postgresql 实现类 merge into 的方法
test=# begin; BEGIN test=# with upsert as ( test(# update target_table test(# set t_name = source_table.s_name test(# from source_table test(# where target_table.t_id = source_table.s_id test(# returning target_table.* test(# ) test-# insert into target_table select * from source_table test-# where not exists ( test(# select 1 test(# from upsert b test(# where source_table.s_id = b.t_id test(# ); INSERT 0 1 test=# select * from target_table order by t_id; t_id | t_name ------+----------- 1 | s_a 2 | s_b 3 | t_c (3 rows) test=# rollback; ROLLBACK
KINGBASE研究院