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

Admin 1
Viewed 3000

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?

insert-into max select mysql sql
2 Answers

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';
user_51361310

Using manual increment on SQL queries can lead to race conditions. You should have the AI (auto increment) constraint when you create your table PK (Primary Key).

    CREATE TABLE table_name
    (
    table_id int NOT NULL PRIMARY KEY AUTO INCREMENT,
    ....
    );

If you absolutely have to use manual increment, use SELECT statement.

user_81894550
Related