How to add 1 to max value INSERT INTO table1 set no=MAX(no)

[jx2]

i've got one "simple" question :)

this doesnt work but i am sure u know what i mean :-)[code=mysql]
INSERT INTO table1 set no=MAX(no)+1;[/code]

this doesnt work too :([code=mysql]
insert into table1 set no=(select MAX(no) from table1)+1;[/code]

any ideas how to do it?

thx a lot
jx2

[pbmods]

Heya, JX2.

If `no` is an auto_incrementi ng primary key, you can simply:
[code=mysql]
INSERT INTO `table1` SET `no` = NULL;
[/code]

If that is not an option, how about this:
[code=mysql]
INSERT INTO `table1` SET `no` = ( SELECT MAX(`no`) + 1 FROM `table1` );
[/code]

[jx2]

heya PBMODS

If `no` is an auto_incrementi ng primary key, you can simply:
[code=mysql]
INSERT INTO `table1` SET `no` = NULL;
[/code]

well taht is not an option (ive allready got one auto increment column)

If that is not an option, how about this:
[code=mysql]
INSERT INTO `table1` SET `no` = ( SELECT MAX(`no`) + 1 FROM `table1` );
[/code]

i tried something like that and i 've got error:
"you cant specify target table 'table1' for update in FROM clause"

sad :-(
any other ideas? there should be an easy way of doing
btw: if i use:
[code=mysql]
INSERT INTO `table2` SET `no` = ( SELECT MAX(`no`) + 1 FROM `table1` );
[/code]
it work fine (but saddly its not what i need :( )

jx2

[pbmods]

Heya, JX2.

The only thing that comes to mind would be to set a session variable:
[code=mysql]
SELECT @newNo := MAX(`no`) + 1 FROM `table1`;
INSERT INTO `table1` SET `no` = @newNo;
[/code]

[jx2]

Heya, JX2.

The only thing that comes to mind would be to set a session variable:
[code=mysql]
SELECT @newNo := MAX(`no`) + 1 FROM `table1`;
INSERT INTO `table1` SET `no` = @newNo;
[/code]

well, i need to explain what i want to achive
1. i dont want to lock the table(no transactions)
2. that will be extremely busy web :-) thats why i dont want to lock tables)
3. there is one auto increment column for each new session
4. there is one registeredNo column (when someone who havent been registered yet is registering i need to assign new registeredNo which should be MAX(registeredN O)
5. i am using PHP/apache/mysql

i hope that make sense :-)

thx
jx2

[kndpro]

SELECT @maxNo := MAX(`no`) + 1 FROM `table1`;
INSERT INTO `table1` SET `no` = (IF(@maxNo IS NULL, 0, @maxNo)) ;

I'm Vietnamese so my English is terrible ! Srr ! But you can try this ! It no error if table have 0 rows ! :D

[nice yoru]

INSERT INTO `table1` (`no`) SELECT ( SELECT MAX(`no`) + 1 FROM `table1` );