auto increment using 'case' in mysql -

i have simple table in mysql has different types of records, differentiated values in column ptype

my table looks this

id1...ptype..usr...item
1.....43.......2......7001
2.....44.......2......8001
3.....43.......2......7002
4.....43.......2......7003
5.....43.......3......7001

when add new record, need query insert auto incremented value in item column, based upon ptype , specific usr. i.e. if insert new record
id1...ptype..usr...item
6.....43.......3......?

it add 1 highest number existing ptype=43 , usr=3 id1...ptype..usr...item
6.....43.......3......7002

if added record ptype=44 , usr=2 id1...ptype..usr...item
7.....44.......2......8002

i think should inserting new record item blank , updating record information derived new record(i.e. @lastid) using case when method, it's not working.

set @lastid := last_insert_id();  set @ptype =  (select `ptype` a1 `id1` = @lastid);  set @item =  (select ( case when @ptype = 41 (select 1 + coalesce((select max(`item`) `a1` `ptype` = 41 , `plate`=7 , `userid` = @userid), 5000)) when @ptype = 42 (select 1 + coalesce((select max(`item`) `a1` `ptype` = 42 , `plate`=7 , `userid` = @userid), 6000)) when @ptype = 43 (select 1 + coalesce((select max(`item`) `a1` `ptype` = 43 , `plate`=7 , `userid` = @userid), 7000)) when @ptype = 44 (select 1 + coalesce((select max(`item`) `a1` `ptype` = 44 , `plate`=7 , `userid` = @userid), 8000))  when @ptype = 45 (select 1 + coalesce((select max(`item`) `a1` `ptype` = 45 , `plate`=7 , `userid` = @userid), 9000))  when @ptype = 46 (select 1 + coalesce((select max(`item`) `a1` `ptype` = 46 , `plate`=7 , `userid` = @userid), 10000))  else 0 end) item a1 `id1` = @lastid); 

update a1 set item = @item id1 = @lastid

as is, @item returning values of 0 initially, no matter 'ptype' new record has, , incrementing 1 subsequent entries.... need first record added in each ptype 5001 6001, 7001, etc.

first, answer didn't ask: reverse idea creating rows in own tables (with auto_increment eggyal suggested) , move data table.

and answer:

your information bit mis-matched, might explain problem or red herring. example, don't describe 'plate' is, use in query. use @userid, not set in examples.

i created table seemed match data @ top:

create table a1 (   id1 int primary key auto_increment,   ptype int,   usr int,   item int ); 

then set variable seemed want:

set @userid = 2; set @ptype = 43; 

and inserted row:

insert a1 (ptype, usr) values (@ptype, @userid); 

pulled id out did:

set @lastid := last_insert_id(); 

then can max 'item':

select max(item) a1  `ptype` = @ptype , `usr` = @userid; 

to handle initial case, wanted default. since you're separating ptypes 1000, can use that:

select ifnull(max(`item`),(@ptype % 40 + 2)*1000)+1 next `a1` `ptype` = @ptype , `usr` = @userid;  +------+ | next | +------+ | 5001 | +------+ 

note isn't thread safe, wrap in transaction/trigger.

hope helps.