Update语句出错，在SQL SERVER中没问题，在DB2中出错！！！

[czwwh]

我有一条SQL语句，在SQL SERVER中通过，在DB2中却报错，帮帮忙看看：


UPDATE H_Acc_Total
SET H_Acc_Total.SyMoney = H_Acc_Total.SyMoney + (B.EndMoney - H_Acc_Total.EndMoney),
H_Acc_Total.EndMoney = B.EndMoney
FROM H_Acc_Total,
(SELECT ROUND(SUM(EndMoney), 4) AS EndMoney, P_CorpID, P_ItemID,P_EntityID, P_Item_UnitID, P_KJQID
FROM H_Acc_Balan
WHERE P_CorpID = 1000 AND P_KJQID =200407
GROUP BY P_ItemID, P_EntityID,P_Item_UnitID, P_CorpID, P_KJQID) B
WHERE B.P_CorpID = H_Acc_Total.P_CorpID AND
B.P_KJQID =H_Acc_Total.P_KJQID AND
B.P_ItemID = H_Acc_Total.P_ItemID AND
B.P_Item_UnitID =H_Acc_Total.P_Item_UnitID AND
B.P_EntityID =H_Acc_Total.P_EntityID AND
B.EndMoney <> H_Acc_Total.EndMoney

提示什么 From什么什么的，难道在DB2中写法不一样？头都搞大了，请高手指点！！！

[czwwh]

我简化一下，看起来更明白一点：
UPDATE H_Acc_Total
SET H_Acc_Total.EndMoney = B.EndMoney
FROM H_Acc_Total,
(SELECT ROUND(SUM(EndMoney), 4) AS EndMoney, P_EntityID,P_KJQID -->这四句是
FROM H_Acc_Balan -->查询结果
WHERE P_CorpID = 1000 -->作为一个
GROUP BY P_EntityID,P_KJQID) B -->临时表
WHERE B.P_KJQID =H_Acc_Total.P_KJQID AND
B.P_EntityID =H_Acc_Total.P_EntityID AND
B.EndMoney <> H_Acc_Total.EndMoney

说明：
目的是把查询出来的汇总字段 EndMoney 去更新表 H_Acc_Total 的EndMoney字段。
在SQL SERVER中这是很简单的一句SQL语句，但在DB2中就是不行，提示不能用FROM

[tx1icenhe]

TRY:

UPDATE H_Acc_Total,
(SELECT ROUND(SUM(EndMoney), 4) AS EndMoney, P_CorpID, P_ItemID,P_EntityID, P_Item_UnitID, P_KJQID
FROM H_Acc_Balan
WHERE P_CorpID = 1000 AND P_KJQID =200407
GROUP BY P_ItemID, P_EntityID,P_Item_UnitID, P_CorpID, P_KJQID) B
SET H_Acc_Total.SyMoney = H_Acc_Total.SyMoney + (B.EndMoney - H_Acc_Total.EndMoney),
H_Acc_Total.EndMoney = B.EndMoney
WHERE B.P_CorpID = H_Acc_Total.P_CorpID AND
B.P_KJQID =H_Acc_Total.P_KJQID AND
B.P_ItemID = H_Acc_Total.P_ItemID AND
B.P_Item_UnitID =H_Acc_Total.P_Item_UnitID AND
B.P_EntityID =H_Acc_Total.P_EntityID AND
B.EndMoney <> H_Acc_Total.EndMoney

没用过，猜的，错了就当我没回答过

[zjcxc]

DB2的不会,我想楼主不如举例说明问题,到DB2版去问问.

[了缘]

UPDATE H_Acc_Total
SET H_Acc_Total.SyMoney = H_Acc_Total.SyMoney + (B.EndMoney - H_Acc_Total.EndMoney),
H_Acc_Total.EndMoney = B.EndMoney
FROM ---这里不用本表吧
(SELECT ROUND(SUM(EndMoney), 4) AS EndMoney, P_CorpID, P_ItemID,P_EntityID, P_Item_UnitID, P_KJQID
FROM H_Acc_Balan
WHERE P_CorpID = 1000 AND P_KJQID =200407
GROUP BY P_ItemID, P_EntityID,P_Item_UnitID, P_CorpID, P_KJQID) B
WHERE B.P_CorpID = H_Acc_Total.P_CorpID AND
B.P_KJQID =H_Acc_Total.P_KJQID AND
B.P_ItemID = H_Acc_Total.P_ItemID AND
B.P_Item_UnitID =H_Acc_Total.P_Item_UnitID AND
B.P_EntityID =H_Acc_Total.P_EntityID AND
B.EndMoney <> H_Acc_Total.EndMoney

[Shires]

up

[Shires]

upup

[寄居蟹]

DB2里的Update语句好象没有from，格式一般是update TABLE set COLUMN1='value' where COLUMN2='value'。你可以试一下，换一个格式。

[godnoloveihavelove]

你从临时表中取的数据没赋给任何值啊

[godnoloveihavelove]

你那个语句可以在SQL SERVER中真的通过吗，觉得也不太可能

[czwwh]

不信你可以试，这样语句在SQL SERVER 中简直是基础语句

[blactte]

db2的Update语句不能关联多表，即不能使用
update a
set a.f2=b.f2
from a,b
where a.f1=b.f1
的形式
可以这样使用
update a
set a.f2=(select f2 from b where b.f2=a.f2)