Merge 用法

需求一：从T1表更新到T2表，如果T2表Name字段的记录在T1表中存在，就将Money字段的值累加，如果不存在，将T1表的记录插入到T2表中。

View Code

Drop Table T1;

Create Table T1(Name Varchar2(20),Money Number);

insert into T1 values ('A',10);

insert into T1 values('B',20);

Drop Table T2;

Create Table T2(Name Varchar2(20),Money Number);

insert into T2 values ('A',30);

insert into T2 values('B',20);

用Merge语句可以简单的实现：

View Code

Merge into T2
Using T1
on (T1.Name=T2.Name)
When Matched Then
Update
Set T2.Money=T1.Money+T2.Money
when not Matched Then
insert
values(T1.Name,T1.Money);

注意：在Oracle 9i中Insert和Update必须同时出现。

在Oracle 10g中完善：

View Code

Merge into T2
Using T1
on (T1.Name=T2.Name)
When Matched Then
Update
Set T2.Money=T1.Money+T2.Money;

也可以仅仅插入：
Merge into T2
Using T1
on (T1.Name=T2.Name)
When not Matched Then
Insert
Values(T1.Name,T1.Money);

可以添加Where条件：

View Code

Merge into T2
Using T1
on (T1.Name=T2.Name)
When Matched Then
Update
Set T2.Money=T1.Money+T2.Money
where T1.Name='A';

案例：Merge 用法注意：无法在源表中获得一组稳定的行

Drop Table T1;
Create Table T1 (Name varchar2(20),Money number);
Insert into T1 values ('A',10);
Insert into T1 values ('A',20);
Insert into T1 values ('B',30);
Drop Table T2;
Create Table T2 (Name varchar2(20),Money number);
Insert into T2 values ('A',30);
Insert into T1 values ('B',20);
commit

此时继续执行如下：
Merge into T2
using T1
on (T1.Name=T2.Name)
when Matched then
update
set T2.Money=T1.Money+T2.Money;

--ORA-30926:无法在源表中获得一组稳定的行

解决办法：

Merge into T2
using (select Name,sum(Money) as Money from T1 Group by Name) T1
on (T1.Name=T2.Name)
when Matched then
update
set T2.Money=T1.Money+T2.Money;
但是 这样可能会改变了原来需求的初衷。

如果将T1中表内容和T2中的内容调换，则执行后成功。结果为：

Name       Money

A           40

A           50

B           50

需求：将如下Test记录Id=1的Name改为Id-2的Name值，将ID=2的Name改为Id=1的Name值。

View Code

drop table test;

create table test(id number,name varchar2(20));

insert into test values (1,'a');

insert into test values (2,'b');

commit;

select * from test;

Id    Name
1       a
2       b

解决方案：
Merge into tes
using (select 1 id, (select name from test where id=2) as name from dual 
union 
select 2 id, (select name from test where id=1)  as name from dual)t
on (test.id=t.id)
when matched then
update set test.name=t.name;

select * from test;

Id     Name
1        b
2        a

 

 

 

 

 

 

转载于:https://www.cnblogs.com/hx8023zx/archive/2012/01/12/2321002.html