表T1有字段part quantity记录分别如下
A001 1000
B001 2000表T2有字段part quantity记录分别如下
A001 100
A001 200现执行update语句
update t1
set t1.quantity=t1.quantity+t2.quantity
from t2
where t1.part=t2.part
按理表T1的A001的数量应该为1300 但结果却为1200为什么,应该怎么改这个语句
A001 1000
B001 2000表T2有字段part quantity记录分别如下
A001 100
A001 200现执行update语句
update t1
set t1.quantity=t1.quantity+t2.quantity
from t2
where t1.part=t2.part
按理表T1的A001的数量应该为1300 但结果却为1200为什么,应该怎么改这个语句
set t1.quantity=t1.quantity+b.quantity
from (select part,sum(quantity) as quantity from t2 group by part) b
where t1.part=b.part
set t1.quantity=t1.quantity+sum(t2.quantity)
from t2
where t1.part=t2.part
set t1.quantity=t1.quantity+b.quantity
from (select part,sum(quantity) as quantity from t2 group by part) b
where t1.part=b.part
set t1.quantity=t1.quantity+sum(t2.quantity)
from t2
where t1.part=t2.part 没有分组,求和得到的数据不对吧.我同意 lwl0606(寒泉) 的sql .
update t1
set t1.quantity=t1.quantity+b.quantity
from (select part,sum(quantity) as quantity from t2 group by part) b
where t1.part=b.part
set t1.quantity=t1.quantity+sum(t2.quantity)
from t2
where t1.part=t2.part