Insert and set value with max()+1 problems

Solution 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.}

Solution 2:

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;

Solution 3:

Use alias name for the inner query like this

INSERT INTO customers
  ( customer_id, firstname, surname )
VALUES 
  ((SELECT MAX( customer_id )+1 FROM customers cust), 'sharath', 'rock')