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.
INSERT INTO customers
( customer_id, firstname, surname )
((SELECT MAX( customer_id ) FROM customers) +1, 'jim', 'sock')
#1093 - You can't specify target table 'customers' for update in FROM clause
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;