I would like help with a before update trigger that conditionally sums the amount column where day and id match, and only display the sum where variable is ‘total’.
id | day | variable | amount -: | :------ | :------- | :----- 1 | Monday | Total | null 1 | Monday | null | 1 1 | Monday | null | 2 1 | Monday | null | 3 1 | Tuesday | Total | null 1 | Tuesday | null | 1 1 | Tuesday | null | 2 1 | Tuesday | null | 3 2 | Monday | Total | null 2 | Monday | null | 1 2 | Monday | null | 2 2 | Monday | null | 3 2 | Tuesday | Total | null 2 | Tuesday | null | 1 2 | Tuesday | null | 2 2 | Tuesday | null | 3
Is there a way to control the sum function so that it won’t update each total unless an associated value has been updated? I.E I wouldn’t want the Monday total to recalculate when values change inside a Tuesday row, or for another ID.
Note that MySQL has a restriction on updating other rows in the same table in a trigger according to the documentation.
A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.
If the following SQL statement will work
UPDATE tb1 t1 JOIN ( SELECT id, day, SUM(amount) as total FROM tb1 t WHERE id = 1 AND day = 'Monday' AND variable is NULL GROUP BY id, day ) t2 ON t1.id = t2.id AND t1.day = t2.day AND t1.variable = 'Total' SET t1.amount = t2.total;
Then its implementation inside the trigger will throw an error
You can’t specify target table ‘tb1’ for update in FROM clause
But you can do it without the trigger and create a view
SELECT id, day, variable, CASE WHEN variable = 'Total' THEN ( SELECT SUM(amount) FROM tb1 t1 WHERE t1.id = t.id AND t1.day = t.day AND t1.variable IS NULL ) ELSE amount END AS amount FROM tb1 t
Or, it is even possible to completely delete all rows with a
Total and retrieve them using the
WITH ROLLUP clause.
SELECT id, day, CASE WHEN amount IS NULL THEN 'Total' END AS variable, SUM(amount) AS amount FROM tb1 GROUP BY id, day, amount WITH ROLLUP HAVING id IS NOT NULL AND day IS NOT NULL