Can't increment an empty set with mysql

Question

I'm trying to write an update that will increment the value of auth_id; however, if there is no value, I'd like to set it to 1. I can't even get the select to give me a 0 value to increment.

mysql> describe sequences;
+---------+--------------------------+------+-----+---------+-------+
| Field   | Type                     | Null | Key | Default | Extra |
+---------+--------------------------+------+-----+---------+-------+
| auth_id | int(5) unsigned zerofill | NO   |     | 00000   |       |
+---------+--------------------------+------+-----+---------+-------+

I thought this would work.

mysql> select ifnull(auth_id,0) from sequences;
Empty set (0.00 sec)

Or this...

mysql> select coalesce(auth_id, 0) from sequences;
Empty set (0.00 sec)

What am I missing?

ifnull() can't create a record from nothing. if there's no records in the tables, there's nothing for ifnull to compare/work on . — Marc B, Jun 06 '16 at 15:05
How many rows do you expect this table to have in total (ever)? — Chris Lear, Jun 06 '16 at 15:10
@ChrisLear 1 row ever. Just incrementing the value of auth_id. — SaintHax, Jun 06 '16 at 15:21

score 0 · Accepted Answer · answered Jun 06 '16 at 15:36

This seems to work:

select ifnull(sequences.auth_id, x.auth_id) as auth_id from sequences right join (select 1 as auth_id) x on 1;

The assumption is that there's only 1 row in sequences. The trick is to make a right join to a fake table with only one row of its own, and then use the ifnull to get the wanted value.

Can't increment an empty set with mysql

1 Answers1