• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
sandeeprajsingh tandon
Ranch Hand
Posts: 80
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 21551
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 80
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Paul. I realize it reading your post that its not a good idea
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic