SQL 使用从同一表计算的值更新表的最佳方法

Best way to update table with values calculated from same table

提问by Rodrigo Lanza

I'm totally noob in sql. I think the best way to do this; I have Receipt table with theese fields:

我完全是 sql 的菜鸟。我认为最好的方法是做到这一点；我有这些字段的收据表：

Receipt
-------
ReceiptID, AssociatedReceiptID, Value, Total

I want to update Total field of all rows with the same AssociatedReceiptID with the sum of their Value fields. So I've tried the next sql statement:

我想用它们的 Value 字段的总和更新具有相同 AssociatedReceiptID 的所有行的 Total 字段。所以我尝试了下一个 sql 语句：

UPDATE Receipt r1
SET Total = (SELECT sum(Value)
             FROM Receipt r2
             WHERE r2.AssociatedReceiptID = r1.AssociatedReceiptID
             GROUP BY r2.AssociatedReceiptID)

With more of 100000 records in this table, it last over 17 hours. Because of I'm updating the same table I'm quering, I decided to split it in two updates statements, storing sum result in a temporary table (which has ) and then updating Receipt table with these values.

该表中有超过 100000 条记录，持续时间超过 17 小时。因为我正在更新我查询的同一个表，所以我决定将它拆分为两个更新语句，将总和结果存储在一个临时表（其中有 ）中，然后使用这些值更新收据表。

UPDATE TemporaryTable t1
SET Total = (SELECT sum(Value)
             FROM Receipt r2
             WHERE r2.AssociatedReceiptID = t1.AssociatedReceiptID
             GROUP BY r2.AssociatedReceiptID)

UPDATE Receipt r1
SET Total = (SELECT Total
             FROM TemporaryTable t1
             WHERE t1.ReceiptID = r1.ReceiptID)

Using these statements, update process takes 6-7 hours. But I'm sure there should be a better way of do this. So, in brief, these are my questions:

使用这些语句，更新过程需要 6-7 个小时。但我相信应该有更好的方法来做到这一点。所以，简而言之，这些是我的问题：

• How do yo do this in a better way?
• Subquery in update statements is executed once per row updated, isn't it? So, if there are 10 rows with same AssociatedReceiptID, sum is calculated 10 times. How can I calculate sum only once per AssociatedReceiptID in the update statement?

• 你如何以更好的方式做到这一点？
• 更新语句中的子查询每更新行执行一次，不是吗？因此，如果有 10 行具有相同的 AssociatedReceiptID，则 sum 计算 10 次。如何在更新语句中为每个 AssociatedReceiptID 仅计算一次总和？

Thanks in advance.

提前致谢。

采纳答案by Yannis

try creating a temp table in memory:

尝试在内存中创建一个临时表：

DECLARE @temp_receipts TABLE (
AssociatedReceiptID int,
sum_value int)

then:

然后：

insert into @temp_receipts
SELECT AssociatedReceiptID, sum(Value)
FROM Receipt
GROUP BY AssociatedReceiptID

and then update the main table totals:

然后更新主表总计：

UPDATE Receipt r
SET Total = (SELECT sum_value
             FROM @temp_receipts tt
             WHERE r.AssociatedReceiptID = tt.AssociatedReceiptID)

However, I would create a table called receipt_totals or something and use that instead. It makes no sense to have the total of each associated receipt in every single related row. if you are doing it for query convenience consider creating a view between receipts and receipt_totals

但是，我会创建一个名为 receive_totals 或其他东西的表并使用它。在每个相关行中包含每个相关收据的总数是没有意义的。如果您是为了查询方便而这样做，请考虑在收据和收据之间创建视图

回答by Hikaru-Shindo

Initially you may go the way you suggest in the question.

最初，您可以按照您在问题中建议的方式进行。

For each change for one row I think it would be better to use database triggers. They will update the value for each row.

对于一行的每个更改，我认为最好使用数据库触发器。他们将更新每一行的值。

You can read about triggers in MySQL here.

您可以在此处阅读 MySQL 中的触发器。

You may need to use InnoDB as a storage Engine.

您可能需要使用 InnoDB 作为存储引擎。

If you do not use MySQL please check the reference corresponding your DBMS.

如果您不使用 MySQL，请检查与您的 DBMS 相对应的参考资料。

回答by Ric Guerrero

I know this is an old question but I think a better way is this.

我知道这是一个老问题，但我认为更好的方法是这样。

UPDATE r1
    SET r1.Total = r2.sumValue

FROM Receipt r1
    INNER JOIN 
        (SELECT sum(Value) sumValue,AssociatedReceiptID
        FROM Receipt rSum
        GROUP BY rSum.AssociatedReceiptID) r2 ON r2.AssociatedReceiptID = r1.AssociatedReceiptID

Here's all in one query and calculaton just one.

这是所有在一个查询和计算中的一个。

Hope it's usefull.

希望它有用。

回答by David Bala?ic

For large tables, copying the table to a new one (and doing the changes at the same time) is much faster (at least on Oracle DB) than updating it.

对于大型表，将表复制到新表（并同时进行更改）比更新它快得多（至少在 Oracle DB 上）。

For example:

例如：

update table1 set some_num = some_num +1 where year = 2010;

is much slower than:

比以下慢得多：

create table table1b as
  select (case when year = 2010 then some_num+1 else some_num) as some_num,
      other, columns, of, the, table
  from table1;
drop table1;
rename table1b to table1; -- also fix or recreate constraints

(this is true also for deleting rows from the table: copy all rows that should remain into a new table and then rename it, instead of a regular DELETE on the original table)

（这对于从表中删除行也是如此：将所有应保留的行复制到新表中，然后重命名，而不是对原始表进行常规 DELETE）

So in your case it would be:

所以在你的情况下，它将是：

create table ReceiptNew as
    select ReceiptID, AssociatedReceiptID, Value,
        sum(value) over (partition by AssociatedReceiptID) 
        as Total
    from Receipt;

drop table Receipt;
rename ReceiptNew to Receipt;

Again, you must re-set the constraints on the table (except "NOT NULL", they get carried over automatically).

同样，您必须重新设置表上的约束（“NOT NULL”除外，它们会自动结转）。

回答by schurik

MERGE INTO Receipt r
USING (
        SELECT sum(Value) s, AssociatedReceiptID
        FROM Receipt           
        GROUP BY AssociatedReceiptID
      ) r_sum 
ON( r.AssociatedReceiptID = r_sum.AssociatedReceiptID)
WHEN MATCHED THEN UPDATE
set r.Total = r_sum.s
;