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

```
None of the about answers works for my case. I got the answer from [here](https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause), 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())
)

```