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

Correct, you can not modify and select from the same table in the same query. You would have to perform the above in two separate queries.

The best way is to use a transaction but if your not using innodb tables then next best is locking the tables and then performing your queries. So:

Lock tables customers write;

$max = SELECT MAX( customer_id ) FROM customers;

Grab the max id and then perform the insert

INSERT INTO customers( customer_id, firstname, surname )
VALUES ($max+1 , 'jim', 'sock')

unlock tables;