CODEWITHSUNDEEP
mysqlsql
Christopher H
Christopher H

Reputation: 2194

SQL Insert Into a MAX value

In SQL how would I insert into a MAX count of another column

So I have this

INSERT INTO USER(id,name,employee_code,email) VALUES (3,john,(SELECT MAX(employee_code)+1 FROM USER),"[email protected]");

However this doesn't work with the syntax... basically it's like an autoincrement that I have to self build because the employee_code sometimes equals 0 for temporary employees so I need a command to take the max code in their and add one.

Upvotes: 1

Views: 14770

Answers (4)

Taryn
Taryn

Reputation: 247680

You need to use an INSERT INTO... SELECT ...FROM query:

INSERT INTO USER(id,name,employee_code,email) 
SELECT 3, 'john', MAX(employee_code)+1, '[email protected]'
FROM `USER`;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Do away with the VALUES statement and use a select instead:

INSERT INTO USER(id,name,employee_code,email)
    SELECT 3, 'john', MAX(employee_code)+1, "[email protected]"
    FROM USER

Upvotes: 3

Matt Smucker
Matt Smucker

Reputation: 5234

INSERT INTO USER(id,name,employee_code,email) 

SELECT 
   3,
   'john', 
   MAX(employee_code)+1, 
   '[email protected]' 
FROM USER

Upvotes: 0

Jocelyn
Jocelyn

Reputation: 11393

You probably forgot quotes around john:

INSERT INTO USER(id,name,employee_code,email)
VALUES (3, 'john', (SELECT MAX(employee_code)+1 FROM `USER`), "[email protected]");

Upvotes: 4

Related Questions