Example of max(id) + 1 in an insert query.

[sandeeprajsingh tandon]

Recording an example of
<code>INSERT INTO customers
( customer_id, firstname, surname )
VALUES
((SELECT MAX( customer_id ) FROM customers C) +1, 'jim', 'sock')</code>

[Paul Clapham]

Is there a question in your post? Because what you've posted there is an example of something which is not a good idea.

The normal thing to do is to set up the customer_id column to be an auto-increment column. Then your INSERT statement should write all of the columns except that; in your case it should only write the firstname and surname columns. After that you can execute another query which tells you the auto-generated values of the customer_id column.

Your example is not a good idea because it can produce duplicate keys if two connections execute that query at about the same point in time. ("Race condition" is the concurrency term for that.) It's also not a good idea because the database can generate the next valid key in a much more efficient way than your "select max" query.

[sandeeprajsingh tandon]

Thanks Paul. I realize it reading your post that its not a good idea