4

I'm trying to develop a script to update the running total within a table:

update transactions set running_total = (select sum(amount) from transactions t2 where t2.sequence_number <= transactions.sequence_number)
  • running_total = the cumulative sum of the amount column
  • sequence_number = the order in which transactions occurred: 1,2,3,4,5...

MySQL is expectedly showing error You can't specify target table 'transactions' for update in FROM clause

How can I re-write this script without using variables (i.e., by relying only on joins, etc.)

Chris
  • 103
  • 6
  • 2
    Try nesting it in another subquery. – shmosel Jan 27 '17 at 22:26
  • Yes, you cannot do that. You can't refer the table you are updating. – MontyPython Jan 27 '17 at 22:26
  • Possible duplicate of [You can't specify target table for update in FROM clause](http://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause) – shmosel Jan 27 '17 at 22:27
  • @Chris - You can check this post on Baron Schwartz's blog too - https://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/ – MontyPython Jan 27 '17 at 22:28

1 Answers1

1

You can do the following:

  • Execute the following query:

    SELECT t.id, t.total, (SELECT SUM(total) FROM transactions WHERE id <= t.id) AS running FROM transactions t;

  • Store the output in a temp table (here is the documentation)

  • Write an update query that will update the records from this table

    UPDATE transactions t SET running_total = (SELECT running FROM temp WHERE id = t.id);

  • Delete the temp table.

For the new INSERTs, you can write the logic in your application (or a BEFORE INSERT trigger) to calculate running total before a row gets inserted.

Here is the SQL Fiddle.

Darshan Mehta
  • 30,102
  • 11
  • 68
  • 102
  • http://sqlfiddle.com/#!9/cd9bda/1 It can be done with temporary table, but its also possible to accomplish the update in a single statement by making use of inline views (derived tables), in place of the separate steps to create, populate and drop the temporary table. – spencer7593 Jan 28 '17 at 00:19
  • @spencer7593 thank you - very helpful & I really appreciate it – Chris Jan 28 '17 at 14:31