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: 
Use table alias in subquery:

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

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