SQL也能这样写

根据一个表的数据update另一个表数据的SQL语句通常如下：
Mysql语法：

update T1 inner join (select ID, TYPE, sum(AMOUNT)
as NEW_AMOUNT from T2 group by ID, TYPE) as T3
on T1.ID = T3.ID and T1.TYPE = T3.TYPE 
set T1.AMOUNT = T1.AMOUNT+T3.NEW_AMOUNT

SqlServer语法：

update T1 set T1.AMOUNT = T1.AMONT+T3.NEW_AMOUNT
from T1 inner join (select ID, TYPE, sum(AMOUNT)
as NEW_AMOUNT from T2 group by ID, TYPE) as T3
on T1.ID = T3.ID and T1.TYPE = T3.TYPE 

但不幸的是上面两种语法均不符合ANSI92的SQL标准，也不能在Hsql中使用。
难道ANSI92不支持这样常用的update功能吗？答案是否，使用如下的SQL语句
即可实现与上面写法相同的功能：

update T1 set T1.AMOUNT = T1.AMOUNT+
(select sum(T2.AMOUNT) from T2 
group by T2.ID, T2.TYPE 
having T2.ID=T1.ID and T2.TYPE=T1.TYPE)

上述SQL在Hsql1.7.2和Mysql5.1下测试成功，测试数据如下：

CREATE TABLE T1 (
ID varchar(255), 
TYPE varchar(255), 
AMOUNT decimal
);
CREATE TABLE T2 (
ID varchar(255), 
TYPE varchar(255), 
AMOUNT decimal
);

delete from T2;
delete from T1;

insert into T2(ID,TYPE,AMOUNT) 
values('0000000001', 'P', 10);
insert into T2(ID,TYPE,AMOUNT) 
values('0000000001', 'P', 20);
insert into T2(ID,TYPE,AMOUNT) 
values('0000000002', 'P', 30);

insert into T2(ID,TYPE,AMOUNT) 
values('0000000001', 'N', 10);
insert into T2(ID,TYPE,AMOUNT) 
values('0000000002', 'N', 20);
insert into T2(ID,TYPE,AMOUNT) 
values('0000000002', 'N', 30);

insert into T1 values('0000000001', 'P', 0);
insert into T1 values('0000000001', 'N', 0);
insert into T1 values('0000000002', 'P', 0);
insert into T1 values('0000000002', 'N', 0);