MySQL, IF statement, variables, and NULL

MySQLnull

After struggling for too long with something that should have been simple, I had a question to ask, but while writing it, I kept looking for solutions and I finally found the solution. I am posting it here anyway in case someone else has the same problem I had.

Description: get max value of a field from database. If that value is null, make it 1, otherwise increase it by one. Insert new record with that value.

Code I was trying:

select @nextNumber := max(number)+1 from test;
if @nextNumber == NULL then @nextNumber := 1;
insert into test (number) values (@nextNumber);

Turns out, the if statement can't stand on its own, at least not here.

Will put working code as an answer.

Best Answer

INSERT INTO test (number)
SELECT COALESCE(MAX(number), 1)
FROM test;

UPDATE

If record(s) exists we need in the next value. So:

INSERT INTO test (number)
SELECT 1 + COALESCE(MAX(number), 0)
FROM test;
Related Question