I assume that the transaction records (more precisely - their base data) are updated NEVER. The alternative (and generally preferred) method for INSERTING into rows that may contain duplicate UNIQUE or PRIMARY. And this allows to perform initial calculation for separate user or users list per chunk easily. But I think this will take less time then the technique with correlated query usage. Of course the whole table will be locked during initial calculation. this trigger needed for balance calculation for newly inserted recordsĬREATE TRIGGER tr_bi_customer_transaction ORDER BY id DESC LIMIT 1 ), 0) + NEW.amount SET NEW.balance = COALESCE( ( SELECT balance or emergency re-calculation (generally for one customer)ĬREATE TRIGGER tr_bu_customer_transaction Look at this: - this trigger needed for initial balance calculation i think, during that execution time, my transaction table remains locked. We are doing those process on a live server and it take's some times to execute. Code of updating of records in the database. Where ct2.customer_id = ct1.customer_id and ct2.id <= ct1.idīut their is a problem. In this tutorial, we will try to learn the about the updating records in the database table in PHP and MySQL. Select sum(case type when 'Credit' then amount when 'Debit' then -amount end) Solution 1: suggested by MySQL 5.x, where window functions are not available, an option uses a correlated subquery to compute the balance: update customer_transaction ct Can i update this by MySQL query or calculate and update from my application (Laravel-PHP). But i'm confused how to recalculate and update all those row. The problem is, on the customer transaction table, their was nearly millions of row and all balance column was 0.00. Now i am introduce a new balance column(current balance) on this table like below. On this table i have 4 columns: id, customer_id, type, amount |id|customer_id |type |amount| On this database i have a table called customer_transaction. I'm using relational database(MySQL 5.7).
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |