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: 
`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](http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html). 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;

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

```