Add one value per row

I want to increment a code +1 every time someone inserts a new row. I was looking at something like:


insert into Table 1 values ((Code='last insert'+1) 'CD003', 'CT', '2003-05-18')((Code='LAST INSERT+1,)----etc.

But it won't work Syntax error. or something like

insert into Table 1 values (SELECT COUNT(Code) FROM
Table 1+1 ) ------etc

cannot quite get my head around how to use them with an insert statement, any ideas please, Chris

Use a sequence:

insert into table1 values (table1_sequence.nextval, 'CD003', 'CT');

which database?

for oracle, use a sequence

for mysql, use an auto_increment field

for access, use an autonumber field

for sql server, use an identity field

et cetera

I have been looking at auto_increment in the manual and from what I understand it needs to be created with the table or inserted with and alter table statement which I cannot do. Is this correct?
I have been working on:

Select code AS last_code
from Guide
ORDER BY code DESC LIMIT 1
insert into Guide values ('last_code+1', 'CD003', 'KN', '2003-05-14');

the select statement gives me the last number I want when run alone and the insert bit works with out the 'last_code' bit I have been looking for a way to put them together and fiddling with brackets with no sucess. I am sure I saw somthing about doing this once but I can not remember where. I am on MySQL, Per, Apache. Any ideas or pointers where to look please, Chris

yes, those are basically the only options -- declared with CREATE TABLE or later with ALTER TABLE

if you are going to SELECT and then INSERT, you need something to hold the value, like a programming language variable

in you example, you have quotes around 'last_code+1', which makes it a string

you'll want to try something like $lastcode+1 (sorry i don't know perl)

Yes thats the lines I was thinking of, but I think I will be on forever debugging! Chris