在一条语句中更新多个值

I have a master / detail table and want to update some summary values in the master table against the detail table. I know I can update them like this:

我有一个主/明细表，并希望在主表中对细节表更新一些汇总值。我知道我可以这样更新它们:

update MasterTbl set TotalX = (select sum(X) from DetailTbl where DetailTbl.MasterID = MasterTbl.ID)
update MasterTbl set TotalY = (select sum(Y) from DetailTbl where DetailTbl.MasterID = MasterTbl.ID)
update MasterTbl set TotalZ = (select sum(Z) from DetailTbl where DetailTbl.MasterID = MasterTbl.ID)

But, I'd like to do it in a single statement, something like this:

但是，我想用一句话来说，大概是这样的:

update MasterTbl set TotalX = sum(DetailTbl.X), TotalY = sum(DetailTbl.Y), TotalZ = sum(DetailTbl.Z)
from DetailTbl
where DetailTbl.MasterID = MasterTbl.ID group by MasterID

but that doesn't work. I've also tried versions that omit the "group by" clause. I'm not sure whether I'm bumping up against the limits of my particular database (Advantage), or the limits of my SQL. Probably the latter. Can anyone help?

但这并不工作。我还尝试了省略“group by”子句的版本。我不确定我是否遇到了特定数据库(优势)的限制，或者SQL的限制。可能后者。谁能帮忙吗?

6 个解决方案

#1

Try this:

试试这个:

 Update MasterTbl Set
    TotalX = Sum(D.X),    
    TotalY = Sum(D.Y),    
    TotalZ = Sum(D.Z)
 From MasterTbl M Join DetailTbl D
    On D.MasterID = M.MasterID

Depending on which database you are using, if that doesn't work, then try this (this is non-standard SQL but legal in SQL Server):

根据您正在使用的数据库，如果不工作，请尝试以下方法(这是非标准SQL，但在SQL Server中是合法的):

 Update M Set
    TotalX = Sum(D.X),    
    TotalY = Sum(D.Y),    
    TotalZ = Sum(D.Z)
 From MasterTbl M Join DetailTbl D
     On D.MasterID = M.MasterID

#2

Why are you doing a group by on an update statement? Are you sure that's not the part that's causing the query to fail? Try this:

为什么要对update语句进行分组?您确定这不是导致查询失败的部分吗?试试这个:

update 
    MasterTbl
set
    TotalX = Sum(DetailTbl.X),
    TotalY = Sum(DetailTbl.Y),
    TotalZ = Sum(DetailTbl.Z)
from
    DetailTbl
where
    DetailTbl.MasterID = MasterID

#3

In Oracle the solution would be:

在Oracle中，解决方案是:

UPDATE
    MasterTbl
SET
    (TotalX,TotalY,TotalZ) =
      (SELECT SUM(X),SUM(Y),SUM(Z)
         from DetailTbl where DetailTbl.MasterID = MasterTbl.ID)

Don't know if your system allows the same.

不知道您的系统是否允许相同。

#4

Have you tried with a sub-query for every field:

您是否尝试过对每个字段进行子查询:

UPDATE
    MasterTbl
SET
    TotalX = (SELECT SUM(X) from DetailTbl where DetailTbl.MasterID = MasterTbl.ID),
    TotalY = (SELECT SUM(Y) from DetailTbl where DetailTbl.MasterID = MasterTbl.ID),
    TotalZ = (SELECT SUM(Z) from DetailTbl where DetailTbl.MasterID = MasterTbl.ID)
WHERE
    ....

#5

Try this:

试试这个:

update MasterTbl M,
       (select sum(X) as sX,
               sum(Y) as sY,
               sum(Z) as sZ,
               MasterID
        from   DetailTbl
        group by MasterID) A
set
  M.TotalX=A.sX,
  M.TotalY=A.sY,
  M.TotalZ=A.sZ
where
  M.ID=A.MasterID

#6

If your DB supports it, concatenating all 3 updates into one sql string will save on server-round-trips if querying over the LAN. So if nothing else works, this might give you a slight improvement. The typical 'multi-statement delimiter is the semi-colon, eg:

如果您的DB支持它，那么将所有3个更新连接到一个sql字符串中，如果在LAN上进行查询，将在服务器往返过程中保存。所以如果没有别的办法，这可能会给你一个小小的改进。典型的“多语句分隔符”是分号，例如:

'update x....;update y...;update...z'