一对多更新如何写出sql语句

[seeupseeup]

例如 t1
ID A B
1 王 0
2 李 5

t2
ID A B
1 王 1
1 王 2
2 李 1
2 李 1

最后根据表 ID 关系 跟新

最后

t1

ID A B
1 王 3
2 李 7
=====================
update t1 set t1.b=t1.b-t2.b from t1,t2 where t1.ID=t2.ID and t1.a=t2.a

这样写 不行......

[AcHerat]

update a
set a.b = a.b + sum(b.b)
from t1 a left join t2 b on a.id = b.id

[seeupseeup]

[Quote=引用 1 楼 acherat 的回复:]

SQL code

update a
set a.b = a.b + sum(b.b)
from t1 a left join t2 b on a.id = b.id
[/Quote]



消息 4104，级别 16，状态 1，第 1 行
无法绑定由多个部分组成的标识符 "t1.b"。
消息 4104，级别 16，状态 1，第 1 行
无法绑定由多个部分组成的标识符 "t2.b"。

[seeupseeup]

[dawugui]

update t1
set b = b + isnull((select sum(b) from t2 where id = t1.id),0)
from t1

[dawugui]

create table t1(ID int,A varchar(10),B int)
insert into t1 values(1 ,'王', 0)   
insert into t1 values(2 ,'李', 5)
create table t2(ID int,A varchar(10),B int)
insert into t2 values(1 ,'王', 1)  
insert into t2 values(1 ,'王', 2)  
insert into t2 values(2 ,'李', 1)
insert into t2 values(2 ,'李', 1)
go

update t1 
set b = b + isnull((select sum(b) from t2 where id = t1.id),0)
from t1

select * from t1

drop table t1 , t2

/*
ID          A          B           
----------- ---------- ----------- 
1           王          3
2           李          7

（所影响的行数为 2 行）
*/

[rucypli]

update t1
set t1.b = t1.b+T3.B
from t1,(select A,sum(B) as B from t2 group by A) t3
where T1.A=T3.A

[Rotel-刘志东]

----sql语句没有问题呀
update a
set a.b = a.b + sum(b.b)
from t1 a left join t2 b on a.id = b.id

[Shawn]

UPDATE A
SET m.B = m.B + n.B
FROM t1 m
INNER JOIN
(
	SELECT ID, A, B=SUM(B) FROM t2 GROUP BY ID, A
) n
ON m.ID = n.ID AND m.A = n.A

[AcHerat]

[Quote=引用 2 楼 seeupseeup 的回复:]
引用 1 楼 acherat 的回复:

SQL code

update a
set a.b = a.b + sum(b.b)
from t1 a left join t2 b on a.id = b.id




消息 4104，级别 16，状态 1，第 1 行
无法绑定由多个部分组成的标识符 "t1.b"。
消息 4104，级别 16，状态 1，第 1 行
无法绑……
[/Quote]

楼主写的不一样把！

[--小F--]

update
 t1  
set
 b = b + isnull((select sum(b) from t2 where id = t1.id),0)
from
 t1

[us_yunleiwang]

WITH CTE_A AS (
select '1' id,'王' A,0 B
UNION
select '2' ,'李' ,5 )
,
CTE_B AS (
SELECT '1' id,'王' A,'1' B
UNION ALL
SELECT '1' ,'王' ,2
UNION ALL
SELECT '2' ,'李' ,1
UNION ALL
SELECT '2' ,'李' ,1 )
/*去掉注释就是结果*/
--UPDATE CTE_A SET B=NUM.SUMN FROM (
select SUM(cte_sum.ctb+cte_SUM.B)OVER(PARTITION BY CTE_SUM.ID) AS SUMN,CTE_SUM.ID,CTE_SUM.CTEA FROM(
SELECT DISTINCT CTE_B.ID,CTE_B.A,CTE_A.B,CTE_A.A AS CTEA,SUM(CTE_B.B) OVER(PARTITION BY CTE_B.ID) as ctb FROM
CTE_B,CTE_A WHERE CTE_A.id=CTE_B.id) cte_sum--)NUM