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.
The code that works:
select @nextNumber := max(number) from test; select @nextNumber := IF(@nextNumber IS NULL, 1, @nextNumber+1); insert into test (number) values (@nextNumber);
This inserts 1 the first time, then 2, then 3, etc. Exactly what I want.
INSERT INTO test (number) SELECT COALESCE(MAX(number), 1) FROM test;