SQL也能这样写

根据一个表的数据update另一个表数据的SQL语句通常如下：
Mysql语法：
[code]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[/code]
SqlServer语法：
[code]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 [/code]

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

[code]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)[/code]

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

[code]CREATE TABLE T1 (
ID varchar(255),
TYPE varchar(255),
AMOUNT decimal
);
CREATE TABLE T2 (
ID varchar(255),
TYPE varchar(255),
AMOUNT decimal
);[/code]

[code]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);[/code]