Best way to update table with values calculated from same table

Question

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

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:

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.

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:

• 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?

Thanks in advance.

Solution 1

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

Solution 2

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.

You may need to use InnoDB as a storage Engine.

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