有两个表:
TableA(TID Number,Qty Number)
TableB(TID Number,Qty Number)关系为:
TableA的主键是:TID, TableA的TID对应多个TableB的TID
我要把 TableA 中Qty 更新为 TableB的Qty加总,
请问这个Update应该怎么写?我可以查询出来:
Select * from TableA A,
(Select TID,Sum(Qty) SumQty) from TableB group by TID) B
Where A.TID=B.TID
TableA(TID Number,Qty Number)
TableB(TID Number,Qty Number)关系为:
TableA的主键是:TID, TableA的TID对应多个TableB的TID
我要把 TableA 中Qty 更新为 TableB的Qty加总,
请问这个Update应该怎么写?我可以查询出来:
Select * from TableA A,
(Select TID,Sum(Qty) SumQty) from TableB group by TID) B
Where A.TID=B.TID
update tablea set qty=(select sum(qty) from tableb where tablea.tid=tableb.tid)
****************************************************** 错误:ORA-01407:无法更新("aa"."TableA"."Qty") 为 NULL
update tablea a set a.qty=(select SumQty from (Select TID,Sum(Qty) SumQty) from TableB group by TID) B where Where A.TID=B.TID)
where exists (select * from (Select TID,Sum(Qty) SumQty) from TableB group by TID) B where Where A.TID=B.TID )记得加上后面的where exists ,要不在tablea 中TID没有包含在tableb会更新为NULL的
update tablea set qty=(select nvl(sum(qty),0) from tableb where tablea.tid=tableb.tid);这是MSSQL的写法
set a.qty=
(select sum(b.qty)
from tableb b
where b.tid=a.tid)
where a.tid in
(select tid
from tableb)