在使用merge 类型的sql 时,就有可能出现这种错误!
建表:
create table area (id INT not null,
area CHAR(10),
constraint PK_AREA primary key (id)
);
create table PEOPLE (
ID INTEGER not null,
NAME CHAR(20),
AREA_ID INT,
constraint PK_PEOPLE primary key (ID)
);
create table PEOPLE_TEMP (
ID INTEGER not null,
NAME CHAR(20),
AREA_ID INT,
constraint PK_PEOPLE primary key (ID)
);
插入数据:
insert into area(id,area) values(1,'北京');
insert into people(Id,name,area_id) values(1,'shang','1');
insert into people(Id,name,area_id) values(2,'wang','1');
insert into people(Id,name,area_id) values(3,'lv','1');
insert into people_temp(Id,name,area_id) values(1,'shangxiaobin','1');
insert into people_temp(Id,name,area_id) values(2,'wang','1');
insert into people_temp(Id,name,area_id) values(3,'lv','1');
merge 的sql
merge into people p
using (select * from people_temp) pt
on p.area_id = pt.area_id
when matched then
update set p.name = pt.name
由于on 条件中 连接条件是 多对多的关系,导致在更新时db2无法判断是更新那一条,所以会出现此异常。