Insert and set value with max()+1 problems

mysql sql insert mysql-error-1093
Cozzy picture Cozzy · Mar 19, 2011 · Viewed 175.4k times · Source

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

EmCo picture EmCo · Mar 19, 2011

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.

Related questions

How to copy a row and insert in same table with a autoincrement field in MySQL?

In MySQL I am trying to copy a row with an autoincrement column ID=1 and insert the data into same table as a new row with column ID=2. How can I do this in a single query?

Read More
MySQL Error 1264: out of range value for column

As I SET cust_fax in a table in MySQL like this: cust_fax integer(10) NOT NULL, and then I insert value like this: INSERT INTO database values ('3172978990'); but then it say `error 1264` out of value for column …

Read More
How to do a batch insert in MySQL

I have 1-many number of records that need to be entered into a table. What is the best way to do this in a query? Should I just make a loop and insert one record per iteration? Or is there …

Read More