Search code examples
mysqlcoalesceis-emptyifnull

Can't increment an empty set with mysql

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?

Solution

  • 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.