SQL 在单个语句中更新多个值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/288894/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update multiple values in a single statement
提问by Kluge
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 的限制。大概是后者。任何人都可以帮忙吗？
回答by Charles Bretana
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
回答by Chris
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
MasterTbl
set
TotalX = Sum(DetailTbl.X),
TotalY = Sum(DetailTbl.Y),
TotalZ = Sum(DetailTbl.Z)
from
DetailTbl
where
DetailTbl.MasterID = MasterID
回答by Dave Costa
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.
不知道您的系统是否允许相同。
回答by Milen A. Radev
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
....
回答by Martin York
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
回答by andora
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:
如果您的数据库支持它，如果通过 LAN 查询，将所有 3 个更新连接成一个 sql 字符串将节省服务器往返行程。因此，如果没有其他任何效果，这可能会给您带来轻微的改进。典型的“多语句分隔符”是分号，例如：
'update x....;update y...;update...z'
