问题描述:
表A(id_a, x)
数据:1, 0表B(id_b, y)
数据:1, 2
数据:1, 4
数据:1, 9
现要做类似如下更新:UPDATE A set x = x + sum(y) from B where id_a=id_b当然,以上sql语句是错误的,执行后表A的数据应为:1,15这个sql语句应该怎么写?
表A(id_a, x)
数据:1, 0表B(id_b, y)
数据:1, 2
数据:1, 4
数据:1, 9
现要做类似如下更新:UPDATE A set x = x + sum(y) from B where id_a=id_b当然,以上sql语句是错误的,执行后表A的数据应为:1,15这个sql语句应该怎么写?
解决方案 »
- 怎么读Text类型的字段值啊
- 如何用installshield实现同时安装MS Server数据库
- 批量重新将其中一个字段更新
- 继续问查询?
- 急,怎样实现数据库的同步.
- 请问跨数据库间的表联接查询,会不会影响性能?
- 关于SSIS包执行过程中报错问题,在线等ing
- 请教在SQL SERVER 2000中把表中的索引字段全部换成自动递增的序号的语句
- SQL Server当中,同时访问或者操纵一个表(此表也是用存储过程写的)时会报错,而同时访问一个表的机率又很高,这个怎么解决?
- 高分求教:如何向一个存储过程传递一个对象??
- 请教一个如何快速更新表内所有记录的问题
- sql中是否有个函数是返回第几周的?
SET x = x + TB.y
FROM TA
INNER JOIN (SELECT id_b, SUM(y) AS y FROM B GROUP BY id_b)TB
ON TA.id_a = TB.id_b
UPDATE A set x = x + b.y from A,
(Select id_b,sum(y) as y from B group by id_b) as b where a.id_a=b.id_b
insert into a select 1, 0
create table B(id_b int,y int)
insert into b select 1, 2
union select 1, 4
union select 1, 9
update A set x=x+B.y from (select id_b,sum(y) as y from B group by id_b) B where id_a=B.id_b
select * from a
--------------------result
id_a x
1 15
go
insert into a select 1, 0
go
create table B(id_b int,y int)
go
insert into b select 1, 2
union select 1, 4
union select 1, 9
gowith temp
as
(
select sum(y) as y,id_b
from B
group by id_b
)
update A
set x = x + y
from A tempA
inner join temp tempB
on id_a = tempB.id_b
go
select * from A
create table A(id_a int, x int)
insert A select 1, 0
go
create table B(id_b int, y int)
insert B select 1, 2
insert B select 1, 4
insert B select 1, 9
goupdate A set x=B.y
from
(
select id_b, y=sum(y)
from B
group by id_b
)as B
where A.id_a=B.id_bselect * from A--result
id_a x
----------- -----------
1 15(1 row(s) affected)
from
(
select id_b, y=sum(y)
from B
group by id_b
)as B
where A.id_a=B.id_b
insert into a select 1, 0create table B(id_b int,y int)
insert into b select 1, 2
union select 1, 4
union select 1, 9update a
set x = t.y
from a, (select id_b,sum(y) as y from b group by id_b) t
where a.id_a = t.id_bselect * from adrop table a,b--result
id_a x
----------- -----------
1 15(所影响的行数为 1 行)
SET x = x + TB.y
FROM TA
INNER JOIN (SELECT id_b, SUM(y) AS y FROM B GROUP BY id_b)TB
ON TA.id_a = TB.id_b
insert A select 1, 0create table B(id_b int, y int)
insert B select 1, 2
union select 1, 4
union select 1, 9
select * from A
select * from Bupdate A set x = x + (select sum(y) from B where A.id_a = B.id_b)
go
if object_id('t') is not null
drop table t
go
create table t(id_a int, x int)
insert t select 1,0
union all select 2,2
go
if object_id('t1') is not null
drop table t1
go
create table t1(id_b int,y int)
insert t1 select 1,2
union all select 1,4
union all select 1,9
union all select 2,5
union all select 2,1
union all select 2,10
go
select * from t
select * from t1
go
update t
set x = x + (select sum(y) from t1 where t.id_a = t1.id_b)
go
select * from t
update a set x =x +(select sum(y) where id_a=id_b)
declare @A table(id_a int,x int)
declare @B table(id_b int,y int)
/*********@A**********/
insert into @A
select 1,0
/*********@A**********/
/*********@B**********/
insert into @B
select 1,2
union all
select 1,4
union all
select 1,9
/*********@B**********/
select * from @A
select * from @Bupdate @A set x=x+b.s
from @A a left join (select id_b,sum(y) as s from @B where id_b=1 group by id_b) b
on a.id_a=b.id_b
select * from @A
declare @A table(id_a int,x int)
declare @B table(id_b int,y int)
/*********@A**********/
insert into @A
select 1,0
/*********@A**********/
/*********@B**********/
insert into @B
select 1,2
union all
select 1,4
union all
select 1,9
/*********@B**********/update @A set x=x+b.s
from @A a left join (select id_b,sum(y) as s from @B group by id_b) b
on a.id_a=b.id_b
select * from @A
set a=t.a from (select a.id_a,sum(b) a from a ,b where a.id_a=b.id_b group by a.id_a )t,a
where a.id_a=t.id_a