mysql - MySQL Fill in the Remaining Balance

Question

I am attempting to calculate the remaining balance and store it in a record in my table. It's complex though since the place where I need to store it is in the table itself. Here is an explanation:

Accounts_Table

╔═════════╦═══════╗
║ Account ║ Total ║
╠═════════╬═══════╣
║    A    ║ $1000 ║
╠═════════╬═══════╣
║    B    ║  $800 ║
╠═════════╬═══════╣
║    C    ║ $1200 ║
╚═════════╩═══════╝

Detail_Table

╔═════════╦══════╦══════╦═════════╗
║ Account ║ Line ║ Type ║  Amount ║
╠═════════╬══════╬══════╬═════════╣
║    A    ║  001 ║   E  ║ $200.00 ║
╠═════════╬══════╬══════╬═════════╣
║    A    ║  002 ║   E  ║ $300.00 ║
╠═════════╬══════╬══════╬═════════╣
║    A    ║  003 ║   E  ║ $100.00 ║
╠═════════╬══════╬══════╬═════════╣
║    A    ║  004 ║   R  ║         ║
╠═════════╬══════╬══════╬═════════╣
║    B    ║  001 ║   E  ║ $300.00 ║
╠═════════╬══════╬══════╬═════════╣
║    B    ║  002 ║   R  ║         ║
╠═════════╬══════╬══════╬═════════╣
║    C    ║  001 ║   R  ║         ║
╚═════════╩══════╩══════╩═════════╝

What am I attempting to do is sum all the Amounts that match each Account, find the difference between that sum and the Total for the Account, and then record that Remaining Balance in the Amount column where the Type Column = R.

So, for example, the Type R line for Account A would become populated with $400.00, which is ($1000.00 - ($300.00 + $200.00 + $100.00)).

So my results would look like:

╔═════════╦══════╦══════╦══════════╗
║ Account ║ Line ║ Type ║  Amount  ║
╠═════════╬══════╬══════╬══════════╣
║    A    ║  001 ║   E  ║  $200.00 ║
╠═════════╬══════╬══════╬══════════╣
║    A    ║  002 ║   E  ║  $300.00 ║
╠═════════╬══════╬══════╬══════════╣
║    A    ║  003 ║   E  ║  $100.00 ║
╠═════════╬══════╬══════╬══════════╣
║    A    ║  004 ║   R  ║  $400.00 ║
╠═════════╬══════╬══════╬══════════╣
║    B    ║  001 ║   E  ║  $300.00 ║
╠═════════╬══════╬══════╬══════════╣
║    B    ║  002 ║   R  ║  $500.00 ║
╠═════════╬══════╬══════╬══════════╣
║    C    ║  001 ║   R  ║ $1200.00 ║
╚═════════╩══════╩══════╩══════════╝

Here's where I am at the moment. This doesn't work and throws an error about updating the same table i'm reading from, not to mention i'm not even sure i'm approaching this problem correctly:

UPDATE Detail_Table 
INNER JOIN Accounts_Table 
ON Detail_Table.Account = Accounts_Table.Account
SET 
Detail_Table.Amount = Accounts_Table.Total - (SELECT SUM(Amount) FROM Detail_Table AS TempTable WHERE TempTable.Account = Accounts_Table.Account) 
WHERE 
Detail_Table.Type = 'R'
AND
Detail_Table.Amount IS NULL;

Any help is appreciated!

标签： mysqlsqlbalance

Answer 1

进行计算的查询是：

select a.account, ( a.total - coalesce(sum(d.amount), 0) ) as balance
from accounts_table a join
     detail_table d
     on a.account = d.account and d.type = 'E'
group by a.account, a.total;

有了这个，只需加入：

update detail_table d left join
       (select a.account, ( a.total - coalesce(sum(d.amount), 0) ) as balance
        from accounts_table a left join
             detail_table d
             on a.account = d.account and d.type = 'E'
        group by a.account, a.total
       ) b
       on d.account = b.account
    set d.amount = b.balance
    where d.type = 'R';