Update query with max function with where clause

Question

I have been trying to get through with the following query:

UPDATE transactions 
SET proposal= MAX(proposal) + 1 
WHERE id =1054

But it shows error. Can anyone help me?

http://stackoverflow.com/questions/3332026/mysql-query-to-update-field-to-maxfield-1 — usha, Dec 04 '14 at 19:08
Try putting parens around your set block: `SET proposal = (MAX(proposal) + 1) — AdamMc331, Dec 04 '14 at 19:12
_"But it shows error"_ - what is the error? Can you post the exact error text? — Krease, Dec 04 '14 at 19:18
thank you very much.http://stackoverflow.com/questions/3332026/mysql-query-to-update-field-to-maxfield-1 is the query I was looking for. — Nazmul Hoque Shafin, Dec 04 '14 at 19:29

score 0 · Answer 1 · answered Dec 04 '14 at 19:26

You probably need a sub-query based on your criteria which is unclear at this point. MAX() is typically based on an aggregate and not what you have. Without knowing more, your query probably needs to be something like...

UPDATE transactions 
   SET proposal = 1 + ( select MAX(proposal) from transactions where SomeCondition )
   WHERE id = 1054

So the inner select MAX() is getting a proposal amount from the transaction table, but I don't know what your criteria is that you need. If the transaction table has many records with id = 1054, then your inner "SomeCondition" would also be... "id = 1054".

if you are trying to compare to another transaction and want the highest, then adjust as needed.

I have tried with your query with insignificant modification.the result is as follows: \ SQL query: Documentation UPDATE transactions SET proposal = 1 + ( select MAX(proposal) from transactions where SomeCondition ) WHERE id = 1051 MySQL said: Documentation #1093 - You can't specify target table 'transactions' for update in FROM clause — Nazmul Hoque Shafin, Dec 04 '14 at 20:02

Update query with max function with where clause

1 Answers1