How to INSERT INTO a MySQL table using ON DUPLICATE KEY UPDATE?

Question

How to INSERT INTO a MySQL table using ON DUPLICATE KEY UPDATE so that if the row already exits one of the columns of the row gets it value plus 1? So, if I have two columns titled ip and count, the count column contains 1 in the first place and increases its value on every next UPDATE. Can we do that in one single statement?

Answer 1

INSERT INTO table(ip,count) VALUES(ip,0)
ON DUPLICATE KEY UPDATE count = count+1

I think it should simply work.

Answer 2

Given two columns, ip and count the query would look like this:

$ip = "10.1.1.1";

$query = "insert into `my_table` (`ip`, `count`) values ('$ip', 1)
on duplicate key update count = count + 1";

This will start with a count of 1 for each new IP, and increment the existing count if it already exists.

Answer 3

MatTheCat, exactly! :) Thanks!