UPDATE within same table

[midnite]

this shouldnt be a difficult problem, and it is quite typical. But just i cant find my way outit is just referring to its own table. as stated in the mysql doc

Another restriction is that currently you cannot modify a table and select from the same table in a subquery. This applies to statements such as DELETE, INSERT, REPLACE, UPDATE, and (because subqueries can be used in the SET clause) LOAD DATA INFILE.

and here seems to be explaining and solving the problem by INNER JOIN. But i dont get it.in fact, what i want to do is: updating the maximum value to a certain value.it will be very nice if the follow query works, but it doesnt!

UPDATE acc_list SET id = 7 WHERE id = (SELECT max(id) FROM acc_list);

so i tried the JOIN method, but it says Invalid use of group function

UPDATE acc_list ac1 inner JOIN acc_list ac2 on ac1.id = max(ac2.id) SET ac1.id = 7;

here's a detailed graphical explanation for my problem:

+----+			  +----+| id |			  | id |+----+			  +----+|  1 |			  |  1 ||  2 |  changes to  |  2 ||  3 |			  |  3 || 18 |			  |  7 |+----+			  +----+

[Yahweh]

so i tried the JOIN method, but it says Invalid use of group function

UPDATE acc_list ac1 inner JOIN acc_list ac2 on ac1.id = max(ac2.id) SET ac1.id = 7;

When you use the Max function (or any other aggregate function like Sum, Min, Average, etc), and you have at least one column not encapsulated by your Max function, then you have to use GROUP BY.Your query is fine, but its missing a GROUP BY clause. Try this:

UPDATE acc_list ac1 inner JOIN acc_list ac2 on ac1.id = max(ac2.id) SET ac1.id = 7 GROUP BY ac1.ID;

[midnite]

oh sorry, it gives me this

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY ac1.id' at line 1

i just wonder if UPDATE queries can use GROUP BY ?