MYSQL How can I update a row in a table after updating the same table?

Question

Each row of my table has a child, For example ID 1 is parent of 11 and 11 is parent of 111 and each row has a balance, I need that if I update the balance of 111, the balance of 11 update and the balance of 1 too

for example: UPDATE ACCOUNTS SET value = 100 WHERE ID = 1 In this case the value of 11 is going to be 100 and the value of 1 then I can do something like this : UPDATE ACCOUNTS SET value = value + 150 WHERE ID = 11; in this case The value of 11 is going to be 250 and the value of 1 will be 250 and the value of 1 should stay 100. I need to do something like that

IM using mySQL

I need something like a trigger for automatically update the parent rows but Update the same table in a trigger is not allowed by myql — Omar Almonte, Oct 26 '20 at 03:29
did you check https://stackoverflow.com/questions/12877732/mysql-trigger-for-updating-same-table-after-insert — Yash Kumar Verma, Oct 26 '20 at 03:39
Does this answer your question? [MySQL - Trigger for updating same table after insert](https://stackoverflow.com/questions/12877732/mysql-trigger-for-updating-same-table-after-insert) — Yash Kumar Verma, Oct 26 '20 at 03:40

score 1 · Accepted Answer · answered Oct 26 '20 at 03:41

1

As you mentioned in the comments, MySQL will generally not allow you to define an update trigger which itself would trigger more updates on the same table. One option here, assuming you are using MySQL 8+, would be to define a recursive CTE which targets all records intended for the update:

WITH RECURSIVE cte (id, value, parent_id) AS (
    SELECT id, value, parent_id
    FROM ACCOUNTS
    WHERE id = 111
    UNION ALL
    SELECT t1.id, t1.value, t1.parent_id
    FROM ACCOUNTS t1
    INNER JOIN cte t2
        ON t1.id = t2.parent_id
)

UPDATE ACCOUNTS a1
INNER JOIN cte a2
    ON a1.id = a2.id
SET value = 100;

This assumes that you would want to do the same update logic for each matching id in the hierarchy. The CTE will generate all records starting from id = 111, and working backwards up the tree to the root.

answered Oct 26 '20 at 03:41

Tim Biegeleisen

502,043
27
286
360

Have some doubs, Can you apply that to my table? The table structure is ```CREATE TABLE `cuentascontables` ( `ID` varchar(255) NOT NULL, `balance` int(255) NOT NULL, `PARENT` varchar(255) NOT NULL, ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;``` – Omar Almonte Oct 26 '20 at 03:55
@OmarAlmonte Sorry for the late reply, but it appears you already figured out that the CTE isn't part of the table definition, it is part of the update code that you would run. – Tim Biegeleisen Oct 26 '20 at 04:01
Its ok, can you APPLY CTE to my table please? I mean the same code but using my column names Thanks in advance – Omar Almonte Oct 26 '20 at 04:02
Again, the CTE should be executed before, and along with, the update statement. A CTE makes no structural changes to your actual code. – Tim Biegeleisen Oct 26 '20 at 04:03
I mean, can you apply CTE with my column names? sorry for bad explanation – Omar Almonte Oct 26 '20 at 04:03
Sorry, I can't do that, because you never told us the column names. – Tim Biegeleisen Oct 26 '20 at 04:10

MYSQL How can I update a row in a table after updating the same table?

1 Answers1