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.