【文章摘要】
编写数据库脚本时经常会用到update语句，对于通过一张表数据更新另一张表数据的语法，各数据库不尽相同。现将我在三种数据库oracle、sybase、sqlserver下测试的测试报告共享，以供参考。
【关键词】
Update 子查询
一、测试环境
Oracle： Oracle 8.1.6.0.0
Sqlserver： Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Sybase： Adaptive Server Enterprise/12.5.0.3/EBF 11449
二、正文
1、测试用例(sqlserver & sybase)
|
create table t1(
id int not null,
cnt1 int default 0 not null,
cnt2 int default 0 not null
)
go
create table t2(
id int not null,
cnt1 int default 0 not null,
cnt2 int default 0 not null
)
go
insert into t1 values(1,0,0)
insert into t1 values(2,0,0)
insert into t1 values(3,0,0)
insert into t1 values(4,0,0)
insert into t2 values(1,10,10)
insert into t2 values(1,20,20)
insert into t2 values(2,5,5)
insert into t2 values(2,15,15)
insert into t2 values(3,11,11)
go
|
2、测试用例(oracle)
|
create table t1(
id int not null,
cnt1 int default 0 not null,
cnt2 int default 0 not null
)
/
create table t2(
id int not null,
cnt1 int default 0 not null,
cnt2 int default 0 not null
)
/
insert into t1 values(1,0,0);
insert into t1 values(2,0,0) ;
insert into t1 values(3,0,0) ;
insert into t1 values(4,0,0) ;
insert into t2 values(1,10,10) ;
insert into t2 values(1,20,20) ;
insert into t2 values(2,5,5) ;
insert into t2 values(2,15,15) ;
insert into t2 values(3,11,11) ;
commit;
|
3、测试要求
将t2.cnt1和t2.cnt2根据id分组后求和，以更新表t1中id相同的记录（t1.cnt1为t2.cnt1之和，t1.cnt2为t2.cnt2之和，且只更新id在两表中均存在的数据）。
4、测试预期结果
表 t1
Id cnt1 cnt2
1 30 30
2 20 20
3 11 11
4 0 0
5、测试语句
Sqlserver
|
--错误：聚合不应出现在 UPDATE 语句的集合列表中
update t1 set cnt1=sum(t2.cnt1),cnt2=sum(t2.cnt2)
from t2
where t1.id=t2.id
--正确写法：
update t1 set cnt1=(select sum(cnt1) from t2 where t1.id=t2.id),
cnt2=(select sum(cnt2) from t2 where t1.id=t2.id)
where exists(select 1 from t2 where t1.id=t2.id)
--正确写法
update t1 set cnt1=a.cnt1,cnt2=a.cnt2
from (select id,sum(cnt1) cnt1,sum(cnt2) cnt2 from t2 group by id)a
where t1.id=a.id
--正确写法
update t1 set cnt1=a.cnt1,cnt2=a.cnt2
from (select id,sum(cnt1) cnt1,sum(cnt2) cnt2 from t2 group by id)a, t1 b
where b.id=a.id
|
Sybase
|
--错误：未报错，查看表t1其所有记录的cnt1和cnt2均为61，不正确
update t1 set cnt1=sum(t2.cnt1),cnt2=sum(t2.cnt2)
from t2
where t1.id=t2.id
--错误：sybase 不支持 from 子句中出现select 子查询
update t1 set cnt1=a.cnt1,cnt2=a.cnt2
from (select id,sum(cnt1) cnt1,sum(cnt2) cnt2 from t2 group by id)a
where t1.id=a.id
--正确写法：
update t1 set cnt1=(select sum(cnt1) from t2 where t1.id=t2.id),
cnt2=(select sum(cnt2) from t2 where t1.id=t2.id)
where exists(select 1 from t2 where t1.id=t2.id)
|
Oracle
|
--错误：无法更新t1.cnt1为null
update t1 set (cnt1,cnt2)=(select sum(t2.cnt1),sum(t2.cnt2)
from t2
where t1.id=t2.id
)
--正确写法：
update t1 set (cnt1,cnt2)=(select sum(t2.cnt1),sum(t2.cnt2)
from t2
where t1.id=t2.id
)where exists(select 1 from t2 where t1.id=t2.id)
|
三、总结
通过一张表数据更新另一张表数据时，各数据库的Update语句不尽相同，主要体现在:Oracle的写法与Sybase、Mssql明显不同； Sybase不支持from 子句中select 子查询。在开发中，尤其是进行数据库脚本移植时需引起注意。