oracle的Merge into语法应用
------------------------目标表
create table zbak_test_person(
id varchar2(32),
name varchar2(256),
heigh varchar2(8),
job_name varchar2(256)
);
----------------------
--插入目标表，测试数据
insert into zbak_test_person (id,name,heigh,job_name) values ('001','test','175','IT');
----------------------
--创建一个临时表，用于中转数据
create table zbak_test_person_temp(
id varchar2(32),
name varchar2(256),
heigh varchar2(8),
job_name varchar2(256)
);
----------------------
--插入临时表，测试数据
insert into zbak_test_person_temp (id,name,heigh,job_name) values ('001','test1','175','IT');
insert into zbak_test_person_temp (id,name,heigh,job_name) values ('002','test2','175','IT');
insert into zbak_test_person_temp (id,name,heigh,job_name) values ('003','test3','175','IT');
insert into zbak_test_person_temp (id,name,heigh,job_name) values ('004','test4','175','IT');
insert into zbak_test_person_temp (id,name,heigh,job_name) values ('005','test5','175','IT');
insert into zbak_test_person_temp (id,name,heigh,job_name) values ('006','test6','175','IT');
insert into zbak_test_person_temp (id,name,heigh,job_name) values ('007','test7','175','IT');
COMMIT;
----------------------
--merge into 语句
merge into zbak_test_person p
using (select>
on (p.id = t.id)
when matched then
update set p.name = t.name, p.heigh = t.heigh, p.job_name = t.job_name
when not matched then
insert values (t.id, t.name, t.heigh, t.job_name);
commit;
----------------------
--验证数据是否更新成功
select * from zbak_test_person;
select * from zbak_test_person_temp;
页:
[1]