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.

  • This is the wrong way to generate sequences. Please see this question – mustaccio Apr 15 at 17:07
  • @mustaccio yeah, that seems overly complex to me. At least for now. – Zonker.in.Geneva Apr 16 at 10:27
INSERT INTO test (number)
FROM test;


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

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

Sounds like you are re-inventing AUTO_INCREMENT. Is there some reason not to use that feature?

  • The way I've designed it, auto-increment does not apply here because the field in question counts from 1 to n and then resets and starts at 1 again each season. This probably means my design could be improved. – Zonker.in.Geneva Apr 24 at 8:21
  • Is there another column that gives the "season"? If so, that should really be part of the Question. Then you have a 2-column PRIMARY KEY (for example). That question has been discussed often. – Rick James Apr 24 at 15:19
  • yeah, based on your comments, clearly my design needs to be greatly improved. Live and learn. I'll go back to the drawing board. – Zonker.in.Geneva Apr 25 at 18:28

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.

  • 1) 3 queries whereas 1 is enough. 2) What if 2 parallel querypacks? – Akina Apr 15 at 9:51
  • I have no idea what you mean by "2 parallel querypacks," so your comment is lost on me. I didn't know about coalesce, so thank you for that. – Zonker.in.Geneva Apr 16 at 10:26
  • I have no idea what you mean by "2 parallel querypacks," I mean that when two (or more) separate processes/client executes the code in question, it is possible that they will obtain and insert the same @nextNumber values. It can be wrong by logic, and ever give dup error if unique index exists. – Akina Apr 16 at 10:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.