Updating a running balance

[Xtremefaith]

Currently trying to update a balance column in my table using this query

UPDATE vendors SET balance = (SELECT SUM(balance) FROM vendors WHERE Code = $ud_payee) + $ud_minDue WHERE Code = $ud_payee

When variables are translated, for testing purpose I would have the following:

UPDATE vendors SET balance = (SELECT SUM(balance) FROM vendors WHERE Code = 'ERL') + 200 WHERE Code = 'ERL'

When I run this I get the following error: #1093 - You can't specify target table 'vendors' for update in FROM clause

So I'm trying to find an optimized workaround. I believe something with inner join is the solution from what I've read so far, but I've never used a join to know how that works at all. Any help with this would be greatly appreciated, thank you so much.

Nick.

[blocblue]

Hi,

If you're balance is already cumulative, why do you need to sum it up again? Could you not simply increment it but the desired amount each time? E.g.

-- Start balance is 0
UPDATE vendors SET balance = balance + 200 WHERE Code = 'ERL';
-- Balance is now 200
UPDATE vendors SET balance = balance + 150 WHERE Code = 'ERL';
-- Balance is now 350
-- Etc, etc.

Cheers,
R.

[Xtremefaith]

Thanks for the response. It is cumulative depending on the situation. Say for example the user edits this transaction, then I would need my update script to recognize the change and update accordingly. (As I'm thinking about it) Would this be easier to calculate the difference in php and then just submit the change in my query?

Also, when I use your code it works perfectly, when I insert my variables like so:

$updatePayee = "UPDATE vendors SET balance = balance + $md WHERE Code = $ud_payee";
	mysql_query($updatePayee) or die(mysql_error());

I get this error: Unknown column 'ERL' in 'where clause'
I even echoed my query to see what I got and this is it: UPDATE vendors SET balance = balance + 200.00 WHERE Code = ERL

Thanks again so much for your help!

[Xtremefaith]

Actually once I saw my own post I realize that I forgot my '' marks, once I added them it worked like a charm. Trying my theory now on calculating the change and only submitting that as you post suggest, thanks!