MYSQL SELECT MAX(id) + 1 where id < 1000

I need to use something like:

 INSERT into tablename(id) 
     SELECT max(id) + 1 
     FROM tablename

where id is less than 1000 and if max(id) = 1000 insert next id as 1, so that the column fills from 1-1000 and start again.

I can have and autoincrement primary key column.

Any help would be greatly appreciated.

I am not an SQL guru, but with the comments below I have done this in Delphi which is where this SQL will be executed. I would still like to know how to do it all in MYSQL and maybe one of the gurus here can help to round this off.

This is what I did:

procedure TForm11.Button1Click(Sender: TObject);
var
lastserial : Integer;
begin
 with LastRowQuery do
  begin
    sql.Clear;
    sql.Add('SELECT autoid, id FROM inctest ORDER BY autoid DESC LIMIT 1;');
    execute;
  end;
  If  LastRowQueryid.value < 1000 then lastserial:=  LastRowQueryid.value + 1
  else
   lastserial := 1;
   with LastRowQuery do
    begin
     sql.Clear;
     sql.Add('Insert into inctest (id) values(:theserial)');
     ParamByName('theserial').Value := lastserial;
   execute;
 end;
end;

Solution

I think maybe you want this: http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_mod

Giving something like:

INSERT into tablename(id) SELECT MOD(count(id),1000)+1 FROM tablename

OR similarly,

INSERT into tablename(id) SELECT (count(id)%1000)+1 FROM tablename

Cheers.