Merger into是oracle9i开始增加的一个函数,用来将两个表的数据合并时,或将一个表的数据添加到另一个表但又不能添加重复数据.
sql语法如下:
merge into table_name table_alias using (table|view|sub_query) alias on(join condition)
when matched then update set col1=col1_val,col2=col2_val......
when not matched then insert (column_list)values(columns_values)
示例:
1.先创建测试表:
create table test1
(
col1 varchar2(200),
col2 varchar2(200),
col3 varchar2(200),
col4 date
);
create table test2
(
col1 varchar2(200),
col2 varchar2(200),
col3 varchar2(200),
col4 date
);
2.表中插入数据:
insert into test1
values('111','121','131',sysdate);
insert into test1
values('112','122','132',sysdate);
insert into test2
values('211','221','231',sysdate);
insert into test2
values('112','122','132',sysdate);
3.
执行前数据查看:
select * from test1;
COL1 | COL2 | COL3 | COL4 |
111 | 121 | 131 | 01-03-2012 11:58:49 |
112 | 122 | 132 | 01-03-2012 11:58:51 |
select * from test2;
COL1 | COL2 | COL3 | COL4 |
211 | 221 | 231 | 01-03-2012 11:58:53 |
112 | 122 | 132 | 01-03-2012 11:58:55 |
执行:
MERGE INTO test2
USING test1
ON(test1.COL1 = test2.COL1)
WHEN MATCHED THEN
UPDATE
SET
test2.COL4=sysdate
WHEN NOT MATCHED THEN
INSERT
VALUES(test1.COL1,test1.COL2,test1.COL3,test1.COL4) ;
执行后结果查看:
test2:
COL1 | COL2 | COL3 | COL4 |
211 | 221 | 231 | 01-03-2012 11:58:53 |
112 | 122 | 132 | 01-03-2012 12:07:49 |
111 | 121 | 131 | 01-03-2012 11:58:49 |