表B id integval cid 1 5 2 2 5 1 3 5 3 4 5 1 5 5 2 表C id integval 1 20 2 20 3 20 现在我想要结果如下: 表C id integval 1 25 2 25 3 25------------- update c set integval=integval +b.integval from c left join b on c.id=b.id
规则不明了,是不是这样? update 表C set integval=(select sum(integval) from 表B)
表B id integval cid 1 5 2 2 5 1 3 5 3 4 5 1 5 5 2 表C id integval 1 20 2 20 3 20 现在我想要结果如下: 表C id integval 1 25 2 25 3 25select c.id,c.integval+b.integval integval from c inner join b on c.id = b.id
create table #1 (id int,integval int,cid int) insert into #1 select 1, 5, 2 union all select 2, 5, 1 union all select 3, 5, 3 union all select 4, 5, 1 union all select 5, 5, 2 create table #2(id int,integval int) insert into #2 select 1, 20 union all select 2, 20 union all select 3, 20update #2 set integval=a.integval+b.integval from #2 a left join #1 b on a.id=b.idselect * from #2id integval ----------- ----------- 1 25 2 25 3 25(3 row(s) affected)
update C set integval=integval+TB.integval from (select distinct integval,cid from B)TB where TB.cid=c.id
update c set integval=integval +b.integval from c inner join b on c.id=b.id
declare @a table(id int identity(1,1),integval int,cid int) insert @a select 5,2 union all select 5,1 union all select 5,3 union all select 5,1 union all select 5,2 declare @b table(id int identity(1,1),integval int) insert @b select 20 union all select 20 union all select 20select c.id,c.integval+d.integval integval from @b c,(select * from @a a where not exists(select 1 from @a b where a.cid=b.cid and a.id>b.id) )d where c.id=d.cid/*(所影响的行数为 5 行) (所影响的行数为 3 行)id integval ----------- ----------- 2 25 1 25 3 25(所影响的行数为 3 行) */
update C set integval=C.integval+TB.integval from (select distinct integval,cid from B)TB where TB.cid=c.id
update c set integval = select sum(integval) from b
create table B(id int,integval int,cid int) insert into B values(1, 5, 2) insert into B values(2, 5, 1) insert into B values(3, 5, 3) insert into B values(4, 5, 1) insert into B values(5, 5, 2) create table C(id int,integval int) insert into C values(1, 20) insert into C values(2, 20) insert into C values(3, 20) goupdate c set integval = (select sum(integval) from b) select * from c /* id integval ----------- ----------- 1 25 2 25 3 25 (所影响的行数为 3 行) */update c set integval = (select sum(integval) from b) from b,c where c.id = b.id select * from c /* id integval ----------- ----------- 1 25 2 25 3 25 (所影响的行数为 3 行) */ drop table B,C/* name name ---------- ---------- 东亚 中国 东亚 韩国 西亚 伊朗(所影响的行数为 3 行)*/
id integval cid
1 5 2
2 5 1
3 5 3
4 5 1
5 5 2
表C
id integval
1 20
2 20
3 20
现在我想要结果如下:
表C
id integval
1 25
2 25
3 25-------------
update c
set integval=integval +b.integval
from c left join b on c.id=b.id
update 表C set integval=(select sum(integval) from 表B)
1.b.cid=c.id
2.c.integeval=c.integeval+b.integval
id integval cid
1 5 2
2 5 1
3 5 3
4 5 1
5 5 2
表C
id integval
1 20
2 20
3 20
现在我想要结果如下:
表C
id integval
1 25
2 25
3 25select c.id,c.integval+b.integval integval from c inner join b on c.id = b.id
create table #1
(id int,integval int,cid int)
insert into #1
select 1, 5, 2 union all
select 2, 5, 1 union all
select 3, 5, 3 union all
select 4, 5, 1 union all
select 5, 5, 2
create table #2(id int,integval int)
insert into #2
select 1, 20 union all
select 2, 20 union all
select 3, 20update #2
set integval=a.integval+b.integval
from #2 a left join #1 b on a.id=b.idselect * from #2id integval
----------- -----------
1 25
2 25
3 25(3 row(s) affected)
from (select distinct integval,cid from B)TB
where TB.cid=c.id
set integval=integval +b.integval
from c inner join b on c.id=b.id
insert @a
select 5,2
union all
select 5,1
union all
select 5,3
union all
select 5,1
union all
select 5,2
declare @b table(id int identity(1,1),integval int)
insert @b
select 20
union all
select 20
union all
select 20select c.id,c.integval+d.integval integval from @b c,(select * from @a a where not exists(select 1 from @a b where a.cid=b.cid and a.id>b.id) )d
where c.id=d.cid/*(所影响的行数为 5 行)
(所影响的行数为 3 行)id integval
----------- -----------
2 25
1 25
3 25(所影响的行数为 3 行)
*/
from (select distinct integval,cid from B)TB
where TB.cid=c.id
set integval = select sum(integval) from b
insert into B values(1, 5, 2)
insert into B values(2, 5, 1)
insert into B values(3, 5, 3)
insert into B values(4, 5, 1)
insert into B values(5, 5, 2)
create table C(id int,integval int)
insert into C values(1, 20)
insert into C values(2, 20)
insert into C values(3, 20)
goupdate c
set integval = (select sum(integval) from b)
select * from c
/*
id integval
----------- -----------
1 25
2 25
3 25
(所影响的行数为 3 行)
*/update c
set integval = (select sum(integval) from b)
from b,c
where c.id = b.id
select * from c
/*
id integval
----------- -----------
1 25
2 25
3 25
(所影响的行数为 3 行)
*/
drop table B,C/*
name name
---------- ----------
东亚 中国
东亚 韩国
西亚 伊朗(所影响的行数为 3 行)*/