[mysql] subquery and update

I wanto set next max number from 'number' field +1 when I update any
record. I tryied to do like this:

UPDATE table set number=(select MAX(number)+1 from table) WHERE Id=122

but I got error 1093

How can I do it? It can't be autoincrement. I must be sure if nobody
else got the same number in the same time.

Re: [mysql] subquery and update

Piotr wrote:
> I wanto set next max number from 'number' field +1 when I update any
> record. I tryied to do like this:
>
> UPDATE table set number=(select MAX(number)+1 from table) WHERE Id=122
>
> but I got error 1093
>
> How can I do it? It can't be autoincrement. I must be sure if nobody
> else got the same number in the same time.

Which version of MySQL?

You could try

SET @new=(SELECT MAX(t.number) FROM table t); UPDATE table SET VALUE=(SELECT
@new := @new+1) WHERE ID=1;



//Aho