Search code examples
mysqlsql-insertsql-max

Insert value in a column conditionally in MySQL

I have a table Persons with columns PersonId, FirstName, DeptId and City. The PersonId is a Primary Key.

By default on first insert, I want the first entry for the DeptId to have a value of 3000. And the subsequent entries should always increment on the last value of DeptId.

Can someone please help me with that in MySQL ?

1 John 3000 Port Mathurin
2 James 3001 Port Desny

insert into `Persons` (`PersonID`, `FirstName`, `DeptId`, `City`) VALUES (1, 'Job', select `DeptId` from `Persons` +1, 'Port Mathurin)
Solution

  • Instead of INSERT ... VALUES use INSERT ... SELECT to select the max DeptId from Persons:

    INSERT INTO Persons (PersonID, FirstName, DeptId, City) 
SELECT 1, 'Job', COALESCE(MAX(DeptId), 2999) + 1, 'Port Mathurin' 
FROM Persons;

    COALESCE() will return 2999 when the table is empty and with +1 the result will be 3000 for the DeptId of the 1st row of the table.