Sql way to update a value using the row before-CodePudding

I have the following table:

I want the initial_balance for the second row (transaction_id = 124) to be the final_balance of the first row (transaction_id = 123), but I just can't.

I tried using this code:

UPDATE transactions
SET initial_balance = (SELECT final_balance 
                       FROM transactions 
                       WHERE transaction_type = 1)
WHERE transaction_id = 124

Which when selected just the part in parentheses does return the value from the table, but the whole code when running results in an error:

Error Code: 1093. You can't specify target table 'transactions' for update in FROM clause

Can anyone help me?

CodePudding user response：

This is a stupid limitation in MySQL. You cannot use the table you are updating or deleting from directly, but must select from it indirectly. That means we have to replace FROM transactions by a ridiculous FROM (select * from transactions) t.

Then, there is also a semantical error in your query, because you forgot to tell the DBMS that you want ID 123.

So either:

UPDATE transactions
SET initial_balance = (SELECT final_balance 
                       FROM (select * from transactions) t
                       WHERE transaction_type = 1
                       AND id = 123)
WHERE transaction_id = 124;

or correlated:

UPDATE transactions
SET initial_balance = (SELECT t.final_balance 
                       FROM (select * from transactions) t
                       WHERE t.transaction_type = 1
                       AND t.id = transactions.id - 1)
WHERE transaction_id = 124;