Re: Avoiding cursors

[Roland Bouman]

I think I'd do this:

update postings p
inner join acclist a1
on p.account = a1.id
inner join acclist a2
on a1.lft >= a2.lft
and a1.rgt <= a2.rgt
set a2.balance = if(a1.id=a2.id,a2.balance + p.amount, a2.balance)
, a2.balancewsubs = if(a1.id=a2.id,a2.balancewsubs, a2.balancewsubs+ p.amount)
;

So, we start off with posting (p), because this drives the action.
Then, we identify the account (a1) that corresponds directly to a particular post.
We use that one to identify all of the accounts in the branch of ancestors (a2), including the original account (a1).

Once we identified the set, we should take care to distinguish two different cases. In case our record corresponds directly with the posting, we add the amount to the balance column. For accounts that are 'higher up' in the branch, we must add the amount to the balancewsubs column. Because sql cannot express that we want to set the one column in the first case, or else the other column, we use an IF to either assign the old value back into the column (mysql is so smart to not do anything in that case) or to perform the addition.

I'm not really a performance guru, but I guess you should have indexes on:

acclist.id
acclist.left, acclist.right

Im not really sure about the last one. Maybe a single index on either left or right is faster. There's not that much possibilities. If performance is paramount, experiment with all options.

Please post back your final solution; someone else might benefit. Thank you!