MySQL, IF statement, variables, and NULL 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.
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;