Search code examples
mysqlsqlselectmaxinsert-into

How to use SELECT MAX inside an INSERT statement in MySQL?

Short Version: How can I get the maximum value of a column and use the result when adding a row with INSERT INTO statement?

Long Version: I have read the question: How to use 'select ' in MySQL 'insert' statement and tried to follow the instructions for its answers. This is my query:

INSERT INTO employee (id, name, state, supervisorId) SELECT MAX(id)+1, 'Dan', state, supervisorId FROM employee WHERE name='Chris';

But I get this error:

ERROR: 1062: Duplicate entry '7' for key 'PRIMARY'

The reason is that I have another row whose id is 7. Consequently, 

MAX(id)

doesn't return the actual maximum value, but a value equal to id of the row containing 'Chris' which is 6.

What am I doing wrong?

Solution

  • You can fix it by replacing this:

    MAX(id)+1

    with this select query:

    (SELECT MAX(id)+1 FROM employee)

    Here's the complete query:

    INSERT INTO employee (id, name, state, supervisorId) SELECT (SELECT MAX(id)+1 FROM employee), 'Dan', state, supervisorId FROM employee WHERE name='Chris';

    Update:

    Although this answer solves the general question about getting the SELECT MAX inside an INSERT query, as @RaymondNijland suggested, it's better to make the most of MySQL auto_increment functionalities. To do so:

    1) Make your primary key column, auto incremented: 

     ALTER TABLE employee MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT;

    2) Remove the id from your insert query:

    INSERT INTO employee (name, state, supervisorId) SELECT 'Dan', state, supervisorId FROM employee WHERE name='Chris';