update a set a.m=b.m from t1 a, ( select id,sum(m) as M from (select * from t1 union all select * from t2) C ) b where a.id=b.id
update t1 set m=m+t3.g from (select id,sum(g) as g from t2 group by id)t3 where t3.id=t1.id
update a set a.m=b.m from t1 a, ( select id,sum(m) as M from (select * from t1 union all select * from t2) C group by id ) b where a.id=b.id
--建立测试环境 create table t1(id int, m int) insert into t1 select 1, 10 insert into t1 select 2,100create table t2(id int, g int) insert into t2 select 1, 2 insert into t2 select 1, 3 insert into t2 select 2, 4 insert into t2 select 2, 5 ------------------------------- update t1 set t1.m = t1.m + t3.g from t1, (select id, sum(g) as g from t2 group by id) as t3 where t1.id = t3.idselect * from t1 drop table t1 drop table t2 --输出结果为: --id m --1 15 --2 109
(
select id,sum(m) as M from
(select * from t1
union all
select * from t2) C
) b
where a.id=b.id
update t1 set m=m+t3.g from (select id,sum(g) as g from t2 group by id)t3 where t3.id=t1.id
(
select id,sum(m) as M from
(select * from t1
union all
select * from t2) C group by id
) b
where a.id=b.id
create table t1(id int, m int)
insert into t1 select 1, 10
insert into t1 select 2,100create table t2(id int, g int)
insert into t2 select 1, 2
insert into t2 select 1, 3
insert into t2 select 2, 4
insert into t2 select 2, 5
-------------------------------
update t1 set t1.m = t1.m + t3.g
from t1, (select id, sum(g) as g from t2 group by id) as t3
where t1.id = t3.idselect * from t1
drop table t1
drop table t2
--输出结果为:
--id m
--1 15
--2 109