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

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?

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')

You can't do it in a single query, but you could do it within a transaction. Do the initial MAX() select and lock the table, then do the insert. The transaction ensures that nothing will interrupt the two queries, and the lock ensures that nothing else can try doing the same thing elsewhere at the same time.

This is select come insert sequel.

I am trying to get serial_no maximum +1 value and its giving correct value.

SELECT MAX(serial_no)+1 into @var FROM sample.kettle;
Insert into kettle(serial_no,name,age,salary) values (@var,'aaa',23,2000);

SELECT MAX(col) +1 is not safe -- it does not ensure that you aren't inserting more than one customer with the same customer_id value, regardless if selecting from the same table or any others. The proper way to ensure a unique integer value is assigned on insertion into your table in MySQL is to use AUTO_INCREMENT. The ANSI standard is to use sequences, but MySQL doesn't support them. An AUTO_INCREMENT column can only be defined in the CREATE TABLE statement:

CREATE TABLE `customers` (
  `customer_id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(45) DEFAULT NULL,
  `surname` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`customer_id`)
)

That said, this worked fine for me on 5.1.49:

CREATE TABLE `customers` (
  `customer_id` int(11) NOT NULL DEFAULT '0',
  `firstname` varchar(45) DEFAULT NULL,
  `surname` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

INSERT INTO customers VALUES (1, 'a', 'b');

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

None of the about answers works for my case. I got the answer from here, and my SQL is:

INSERT INTO product (id, catalog_id, status_id, name, measure_unit_id, description, create_time)
VALUES (
  (SELECT id FROM (SELECT COALESCE(MAX(id),0)+1 AS id FROM product) AS temp),
  (SELECT id FROM product_catalog WHERE name="AppSys1"),
  (SELECT id FROM product_status WHERE name ="active"),
  "prod_name_x",
  (SELECT id FROM measure_unit WHERE name ="unit"),
  "prod_description_y",
  UNIX_TIMESTAMP(NOW())
)

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

Use table alias in subquery:

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

We declare a variable 'a'

SET **@a** = (SELECT MAX( customer_id ) FROM customers) +1;

INSERT INTO customers
  ( customer_id, firstname, surname )
VALUES 
  (**@a**, 'jim', 'sock')

insert into table1(id1) select (max(id1)+1) from table1;

Your sub-query is just incomplete, that's all. See the query below with my addictions:

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