mysql - Insert and set value with max()+1 problems

Question

I am trying to insert a new row and set the customer_id with max()+1. The reason for this is the table already has a auto_increatment on another column named id and the table will have multiple rows with the same customer_id.

With this:

INSERT INTO customers
  ( customer_id, firstname, surname )
VALUES 
  ((SELECT MAX( customer_id ) FROM customers) +1, 'jim', 'sock')

...I keep getting the following error:

#1093 - You can't specify target table 'customers' for update in FROM clause

Also how would I stop 2 different customers being added at the same time and not having the same customer_id?

Answer 1

You can use the INSERT ... SELECT statement to get the MAX()+1 value and insert at the same time:

INSERT INTO 
customers( customer_id, firstname, surname )
SELECT MAX( customer_id ) + 1, 'jim', 'sock' FROM customers;

^{Note: You need to drop the VALUES from your INSERT and make sure the SELECT selected fields match the INSERT declared fields.}