SaintHax SaintHax - 6 months ago 8
SQL Question

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?

Answer

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.