MySQL, IF statement, variables, and NULL

by Zonker.in.Geneva   Last Updated April 15, 2019 10:06 AM

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.

Tags : mysql null


Answers 2


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.

Zonker.in.Geneva
Zonker.in.Geneva
April 15, 2019 09:13 AM

INSERT INTO test (number)
SELECT COALESCE(MAX(number), 1)
FROM test;
Akina
Akina
April 15, 2019 09:50 AM

Related Questions





Why doesn't FIND_IN_SET return on NULL values

Updated May 22, 2017 12:06 PM

MYSQL only Show columns which allow null

Updated April 19, 2018 08:06 AM