sql - How do you SELECT MAX() inside an INSERT in MYSQL to automatically increase the customerNumber

Question

I have been trying to add a new row in the table which will add the following but then automatically increment the customerNumber by one to a new value from the table. I have tried it out with variables and it worked but I need to do this query in one operation.

INSERT INTO customers (customerNumber,customerName, contactLastName, contactFirstName, phone, addressLine1, addressLine2, city, state, postalCode, country)
VALUES ((SELECT MAX(customerNumber)+1 FROM customers),'Detectives Inc.', 'Holmes', 'Sherlock', '2743824490', '221B Baker Street', 'Apt 2', 'London', 'LL', '50876', 'England');

This is what I have been trying but I get an error "You can't specify target table 'customers' for update in FROM clause"

EDIT

I resolved the issue by doing the following query

INSERT INTO customers (customerNumber,customerName, contactLastName, contactFirstName, phone, addressLine1, addressLine2, city, state, postalCode, country) SELECT MAX(customerNumber)+1 ,'Detectives Inc.', 'Holmes', 'Sherlock', '2743824490', '221B Baker Street', 'Apt 2', 'London', 'LL', '50876', 'England' FROM customers;

question from:https://stackoverflow.com/questions/65838728/how-do-you-select-max-inside-an-insert-in-mysql-to-automatically-increase-the

Answer 1

You can use sub-query and try as follows:

...
VALUES ((SELECT MAX(customerNumber)+1 from (select customernumber FROM customers) t),'Detectives Inc.'
...