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: 
You can use the [`INSERT ... SELECT`](https://dev.mysql.com/doc/refman/5.7/en/insert-select.html) 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.
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')

```