VT’s Tech Blog

Here’s a quick tip in MySQL. Let’s say you have a table called browsercount like the one below:

Let’s say you have a web-analytic application which has to add a browser into the table if it doesn’t exist, but update the count for the browser if the entry already exists. How do you go about that?

The usual way would be to first query the table to see if the entry exists, then fire an insert statement or an update statement based on the results.

Here’s how to perform the same action in one SQL statement:

INSERT INTO browsercount (browser,count) VALUES ('Firefox',1) ON DUPLICATE KEY UPDATE count=count+1;

This will work if the id field in the table above is declared as UNIQUE or is a Primary Key.

The query basically tells mysql to insert the data into the database, but if a duplicate key is found, increment the count field by 1. It’s much faster than firing up 2 sql queries from your PHP script