infomix语法测试。
create table test1
(
id int,
counter1 int,
counter2 int,
counter3 int
);
insert into test1(id,counter1,counter2,counter3) values (1,0,0,0);
insert into test1(id,counter1,counter2,counter3) values (2,0,0,0);
insert into test1(id,counter1,counter2,counter3) values (3,0,0,0);
insert into test1(id,counter1,counter2,counter3) values (4,0,0,0);
create table test2
(
id int,
counter1 int,
counter2 int
);
insert into test2(id,counter1,counter2) values (1,111,111);
insert into test2(id,counter1,counter2) values (2,222,222);
insert into test2(id,counter1,counter2) values (3,333,333);
update test1 set (counter1,counter2,counter3)=
((select counter1,counter2,counter1+counter2 from test2
where test2.id=test1.id))
where test1.id in (select id from test2)
select * from test1
执行结果:
id counter1 counter2 counter3
1 111 111 222
2 222 222 444
3 333 333 666
4 0 0 0
正是俺想要的结果,不过这个update的写法比较变态,远比MSSQL复杂,估计效率也不高。