title: MySQL: Update a running "account balance" based on another table
body: <p><strong>Fiddle:</strong>
  Please see this SQLFiddle: <a href="http://sqlfiddle.com/#!9/af05d8/1/0" rel="nofollow noreferrer">http://sqlfiddle.com/#!9/af05d8/1/0</a></p>
  
  <p><strong>Scenario:</strong> I need to update customer account balances based on a table listing the jobs/services received. Each customer account should be charged a given price for each job/service, by adding a new row to the Ledger table with the new customer account balance (previous_balance - price).</p>
  
  <p><strong>Issue:</strong> The fiddle / queries work fine when a customer only has one job in the table requiring a balance update. However when the customer has more than one row in the jobs table, the actual account balance isn't used for each job - the original account balance is used for all jobs instead.</p>
  
  <p><em>edit to clarify</em>: the issue has been identified by spencer5973 in answer #1. The SELECT is running against the table as it sits at the start of the statement. New rows added to the Ledger by the query are not taken into account for subsequent SELECTs, meaning the correct running total is not SELECTed for customers with more than one row in the jobs table.</p>
  
  <p><strong>Detail:</strong></p>
  
  <p><em>tmptblLedger</em>: a running list of account balances (Ledger entries) for all Customers. Each time the Customer account balance is changed (payment, purchases etc), a new row is added. Each row records the opening balance - which is the same as the previous closing balance - and the new closing balance.</p>
  
  <p><em>edit to clarify:</em> the Customer account balance can be reduced by paying for a Job (as shown in the fiddle and this question), or increased by a payment to account (not shown), or otherwise altered by a transaction unrelated to the Job table. This prevents spencer5973's answer below from working. The Ledger is the one single authoritative source of all transactions/adjustments to customer account balances.</p>
  
  <p><em>tmptblJobs</em>: a "chargeable work done" tracking table. Each time the Customer has some work done (a job), a row is added. The row specifies how many jobs were done, and the price per job.</p>
  
  <p>The requirement is pretty simple - each Customer's Ledger has to be updated to reflect the jobs done. In pseudocode:</p>
  
  <pre><code>foreach(row in tmptblJobs)
  {
      get row.Customer ID;
      get Customer's current account balance (MAX LedgerID in Ledger table)
      new_balance = subtract (row.NumberOfJobs * row.PricePerJob) from current balance
      write new row with new_balance to ledger table
  }
  </code></pre>
  
  <p>The queries work correctly when a Customer has only one row in the Jobs table. </p>
  
  <p>The issue seems to be that when a Customer has more than 1 row in the Jobs table, the new row written to the Ledger table (new balance) is ignored for subsequent rows in the Jobs table. Instead, the original balance before the query ran is used - like the result is cached.</p>
  
  <p>I can believe I'm missing some SQL or feature to make this work, but I don't know what...</p>
  
  <p><strong>Table structure and sample data:</strong></p>
  
  <pre><code>DROP TABLE IF EXISTS tmptblLedger;
  DROP TABLE IF EXISTS tmptblJobs;
  
  CREATE TABLE `tmptblLedger` (
    `LedgerID` int(10) UNSIGNED NOT NULL COMMENT 'Unique transaction ID',
    `Timestamp` DATETIME,
    `BalanceOpen` float NOT NULL COMMENT 'Last balance for customer before this record was created',
    `BalanceClose` float NOT NULL  COMMENT 'Balance now that record has been created = BalanceOpen + Adjustment Amount',
    `Customer_CustID` int(10) UNSIGNED NOT NULL
  );
  ALTER TABLE `tmptblLedger` 
      MODIFY `LedgerID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1,
      ADD UNIQUE KEY (`LedgerID`);
  
  /* some representative data, this example only uses Customer_CustID == 1). All ledgers start at zero, this example has a second ledger entry for CustID == 1 to put the balance to 100 */
  INSERT INTO `tmptblLedger` (`Timestamp`, `BalanceOpen`, `BalanceClose`, `Customer_CustID`)  VALUES (NOW(), 0, 0, 1), (NOW(), 0, 100, 1), (NOW(), 0, 5, 2), (NOW(), 0, 7, 3);
  
  CREATE TABLE `tmptblJobs` (
    `ScheduledTargetID` bigint(20) NOT NULL,
    `Customer_CustID` int(10) UNSIGNED NOT NULL,
    `NumberOfJobsCompleted` int UNSIGNED,
    `PricePerJob` float UNSIGNED
  );
  ALTER TABLE `tmptblJobs`
      MODIFY `ScheduledTargetID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1,
      ADD UNIQUE KEY(`ScheduledTargetID`);
  
  /* insert 3 jobs for CustID 1, and an example 1 job for CustID 2 to represent real world data */
  INSERT INTO `tmptblJobs` (`Customer_CustID`, `NumberOfJobsCompleted`, `PricePerJob`)    VALUES (1, 2, 5), (1, 1, 3), (1, 1, 1), (2, 1, 1);
  </code></pre>
  
  <p><strong>Query:</strong></p>
  
  <pre><code>INSERT INTO `tmptblLedger` (`Timestamp`, `BalanceOpen`, `BalanceClose`, `Customer_CustID`)
      SELECT
          NOW(),
          derivedLedger.LedgerCurrentBalance,
          derivedLedger.LedgerCurrentBalance - ( @TotalPrice:=(tmptblJobs.NumberOfJobsCompleted * tmptblJobs.PricePerJob) ) AS NEWBALANCE,
          tmptblJobs.Customer_CustID
      FROM
          (
              /* Obtain current balance for each CustID, using newest LedgerID */
              SELECT
                  derivedNewestLedgerRow.LedgerCustID,
                  tmptblLedger.BalanceClose AS LedgerCurrentBalance /* current balance == closing balance of newest ledger entry */
              FROM
                  tmptblLedger
              RIGHT JOIN
              (
                  /* Obtain newest LedgerID for each CustID */
                  SELECT
                      tmptblLedger.Customer_CustID AS LedgerCustID,
                      MAX(LedgerID) AS NewestCustLedgerID
                  FROM
                      tmptblLedger
                  GROUP BY tmptblLedger.Customer_CustID
              ) as derivedNewestLedgerRow ON LedgerID = NewestCustLedgerID
          ) as derivedLedger
      INNER JOIN tmptblJobs ON tmptblJobs.Customer_CustID = derivedLedger.LedgerCustID
      WHERE 1;
  
  /* LegerID rows 5, 6 and 7 will now all start from a BalanceOpen of 100, instead of BalanceOpen 100, then 90 (for ScheduledTargetID 1), then 87 (ScheduledTargetID 2), then 86 (ID 3)... */ 
  SELECT * FROM tmptblLedger;
  </code></pre>
  
  <p><strong>Results:</strong></p>
  
  <pre><code>LedgerID    Timestamp   BalanceOpen BalanceClose    Customer_CustID
  1   2019-03-06T16:03:11Z    0   0   1
  2   2019-03-06T16:03:11Z    0   100 1
  3   2019-03-06T16:03:11Z    0   5   2
  4   2019-03-06T16:03:11Z    0   7   3
  5   2019-03-06T16:03:11Z    100 90  1
  6   2019-03-06T16:03:11Z    100 97  1
  7   2019-03-06T16:03:11Z    100 99  1
  8   2019-03-06T16:03:11Z    5   4   2
  </code></pre>
  
  <p>Rows 5, 6 and 7 are wrong. BalanceClose should be:</p>
  
  <pre><code>Row 5 = 90
  Row 6 = 87
  Row 7 = 86
  </code></pre>