Decrement-or-delete in SQL

Question

In SQL, I've got a table that maps token=>count; it's a dict where the default value is 0. If I want to increment a token, I can say:

insert into my_table (token, count)
values (my_token, 1)
on duplicate key update count = count + 1;

and if a token doesn't exist yet, it's inserted with count=1. Nifty.

Is there a similarly easy way to do the opposite in SQL, i.e., "given a token, decrement its count, and if count becomes 0 then just remove the record entirely"? I can think of longer ways to do it, but nothing as concise as the above.

Answer 1

You want triggers. (I take it this is MS SQL).

Answer 2

Um, wouldn't the answer be the obvious:

insert into my_table (token, count)
values (my_token, 0) 
on duplicate key update count = count - 1;

Answer 3

My advice is to use transactions as follows (my SQL is a bit rusty but you should get the idea):

-- begin transaction
update my_table set count = count - 1 where id = '7'
delete from my_table where id = '7' and count = 0
commit

This will ensure the atomicity of the decrement-and-delete operation.

However, one other possibility you may want to consider - don't remove it at the point where it reaches zero. Since you say the default value is zero anyway, just leave the row in there with a value of 0.

Of course, your queries will need to change to adapt to that. If you have one that lists active tokens, it will change from:

select token from my_table

to:

select token from my_table where count > 0

The decrement SQL in that case must be careful not to push the token count down to -1, so it would become:

update my_table set count = count - 1 where id = '7' and count > 0

That simplifies your SQL at the decrement time. If you still want those rows with zero-count to disappear, you could have another process that runs periodically to sweep them all up:

delete from my_table where count = 0

That's just some alternatives to consider - if you really want them gone at the exact time their count reaches zero, then use the transaction method above.