I'm using relational database(MySQL 5.7). On this database i have a table called customer_transaction. On this table i have 4 columns: id, customer_id, type, amount
|id|customer_id |type |amount|
|--|------------|---------|------|
|1 |44 |Credit |50 |
|2 |44 |Credit |20 |
|3 |44 |Debit |30 |
|4 |10 |Credit |30 |
now i am introduce a new balance column(current balance) on this table like below.
|id|customer_id |type |amount|balance|
|--|------------|---------|------|-------|
|1 |44 |Credit |50 |50 |
|2 |44 |Credit |20 |70 |
|3 |44 |Debit |30 |40 |
|4 |10 |Debit |30 |-30 |
The problem is, on the customer transaction table, their was nearly millions of row and all balance column was 0.00.
So i want to re-sync all balance data. But i'm confused how to recalculate and update all those row. Can i update this by MySQL query or calculate and update from my application (Laravel-PHP).
Solution 1: suggested by @GMB
In MySQL 5.x, where window functions are not available, an option uses a correlated subquery to compute the balance:
update customer_transaction ct
inner join (
select
id,
(
select sum(case type when 'Credit' then amount when 'Debit' then -amount end)
from customer_transaction ct2
where ct2.customer_id = ct1.customer_id and ct2.id <= ct1.id
) balance
from customer_transaction ct1
) ctx on ctx.id = ct.id
set ct.balance = ctx.balance
But their is a problem. We are doing those process on a live server and it take's some times to execute. i think, during that execution time, my transaction table remains locked. Is their any way to preventing locking.
ORDER BY transaction_time
and set balance to NULL or 0 - it will be recalculated (and in future this triggers will set correct balance values for newly inserted records and store your table in actual state).