Photo by Walsh

I needed to keep track of how many emails a user had sent on a given day in my database, and it was surprisingly tough to implement. As I came across a message, I needed to add one to the total for that user on that day. If a record for that time and address already exists, it’s simple:

UPDATE messagefrequencies SET senttocount=senttocount+1
WHERE address=currentaddress AND day=currentday;

When I implemented that, I realized that it didn’t do quite what I wanted. If there wasn’t already a record for that address and day, the update would fail. There were a potentially massive number of combinations of days and addresses, and I didn’t want to create blank rows for all of them, so I needed some way to increment the current value if it already exists, or create it and set it to 1 if there isn’t a row that matches.

My first attempt was to use the IF EXISTS syntax, but I discovered that’s only valid within stored procedures. The real solution turned out to be the opposite of the way I was thinking about the problem, since there’s an ON DUPLICATE KEY command that lets you attempt a row INSERT and then if the row already exists you can do an update. One thing to watch out for is that this update syntax doesn’t require a SET, instead you just specify the columns you want to change.

INSERT INTO messagefrequencies (day, address, senttocount)
VALUES (TO_DAYS(‘2006-05-29 13:59:10’), ‘somebody@gmail.com’, ‘1’)
ON DUPLICATE KEY UPDATE senttocount = senttocount+1;