create table ttt( id int, id1 int )create table ttt1( id int, id1 int ) insert ttt1 select 1,0insert ttt select 1,1 union select 1,2 union select 1,3--update a set a.id1=a.id1+b.id1 from ttt1 a,ttt b where a.id=b.id update a set a.id1=a.id1+b.id1 from ttt1 a,(select id,sum(id1) as id1 from ttt group by id) b where a.id=b.idselect * from ttt1drop table ttt drop table ttt1
update a set a.id1 = a.id1 + b.id1 from ttt1 a, (select id, id1 = sum(id1) from ttt group by id) b where a.id = b.id
update a set a.id1 = a.id1 + b.id1 from ttt1 a, (select id, id1 = sum(id1) from ttt group by id) b where a.id = b.id
create table ttt( id int, id1 int )create table ttt1( id int, id1 int ) insert ttt1 select 1,0insert ttt select 1,1 union select 1,2 union select 1,3--update a set a.id1=a.id1+b.id1 from ttt1 a,ttt b where a.id=b.id --update a set a.id1=a.id1+b.id1 from ttt1 a,(select id,sum(id1) as id1 from ttt group by id) b where a.id=b.id update ttt1 set id1=(select sum(id1) from ttt where id=ttt1.id) select * from ttt1drop table ttt drop table ttt1
UPDATE ttt1 SET id1 = b.id1 FROM ttt1 AS a, ( SELECT a.id,SUM(b.id1) AS id1 from ttt1 a,ttt b where a.id=b.id GROUP BY a.id ) AS b WHERE a.id=b.id
update a set a.id1=b.id1 from ttt1 a inner join (select id,sum(id1) as id1 from ttt group by id) b on a.id=b.idname 字段不知道你是怎么取的
--不好意思,我3楼写错了 create table ttt( id int, id1 int )create table ttt1( id int, id1 int ) insert ttt1 select 1,0insert ttt select 1,1 union select 1,2 union select 1,3update ttt1 set id1=id1+(select sum(id1) from ttt where id=ttt1.id) select * from ttt1drop table ttt drop table ttt1
这个结论对吗:update更新时,如果有多条记录匹配,以最后一条为准
create table ttt( id int, id1 int )create table ttt1( id int, id1 int ) insert ttt1 select 1,0insert ttt select 1,1 union select 1,2 union select 1,3
--测试 update a set a.id1=b.id1 from ttt1 a,(select id, sum(id1) as id1 from ttt group by id) b where a.id=b.id
select * from ttt1 drop table ttt,ttt1--结果 id id1 ----------- ----------- 1 6(1 row(s) affected)
id int,
id1 int
)create table ttt1(
id int,
id1 int
)
insert ttt1
select 1,0insert ttt
select 1,1
union
select 1,2
union
select 1,3--update a set a.id1=a.id1+b.id1 from ttt1 a,ttt b where a.id=b.id
update a set a.id1=a.id1+b.id1 from ttt1 a,(select id,sum(id1) as id1 from ttt group by id) b where a.id=b.idselect * from ttt1drop table ttt
drop table ttt1
id int,
id1 int
)create table ttt1(
id int,
id1 int
)
insert ttt1
select 1,0insert ttt
select 1,1
union
select 1,2
union
select 1,3--update a set a.id1=a.id1+b.id1 from ttt1 a,ttt b where a.id=b.id
--update a set a.id1=a.id1+b.id1 from ttt1 a,(select id,sum(id1) as id1 from ttt group by id) b where a.id=b.id
update ttt1 set id1=(select sum(id1) from ttt where id=ttt1.id)
select * from ttt1drop table ttt
drop table ttt1
FROM
ttt1 AS a,
(
SELECT a.id,SUM(b.id1) AS id1
from ttt1 a,ttt b where a.id=b.id
GROUP BY a.id
) AS b
WHERE a.id=b.id
create table ttt(
id int,
id1 int
)create table ttt1(
id int,
id1 int
)
insert ttt1
select 1,0insert ttt
select 1,1
union
select 1,2
union
select 1,3update ttt1 set id1=id1+(select sum(id1) from ttt where id=ttt1.id)
select * from ttt1drop table ttt
drop table ttt1
id int,
id1 int
)create table ttt1(
id int,
id1 int
)
insert ttt1
select 1,0insert ttt
select 1,1
union
select 1,2
union
select 1,3
--测试
update a set a.id1=b.id1 from ttt1 a,(select id, sum(id1) as id1 from ttt group by id) b where a.id=b.id
select * from ttt1
drop table ttt,ttt1--结果
id id1
----------- -----------
1 6(1 row(s) affected)