Hey Guys,
Let me first explain you the scenario in which more likely you can use this type of query. A generic scenario would be that you are inserting data into a table and along with this you want to update a counter which is nothing but a field in the same table. More precisely or by taking an example, you may want to use this query when you want to count how many times a User has logged into your website. Or how many times a particular IP or user has visited soem XYZ page on your website. So conventionally, we will insert a query into a log table and then fire an Update query which will update the counter by +1.
Update Numeric Counter in a table :
Lets create a table first :
create table myUserTable (userId int not null primary key, logged_counts int not null, login_time int); |
Now as discussed above in terms of conventional mysql query it would be :
insert into myUserTable (userId,login_time) values(3421,432432); update myUserTable set logged_counts=logged_counts+1; |
You can achieve this using on duplicate key as follows :
insert into myUserTable (userId,login_time)values(3421,3122) on DUPLICATE KEY UPDATE logged_counts=logged_counts+1; -- if the table is empty it will insert 0 as the first value, as long as u do not have default as 1 |
Update String values in a table
An update in the example after EllisGL’s comment here on dzone
He said that no example on internet shows how to update columns with string values other then just increasing the counter. So here we go with the following example, which updates Email address and gtalk ID in the table, when the userId is same 🙂
create table myUserTable (userId int not null primary key,email varchar(30),gtalk varchar(20)); insert into myUserTable (userId,email,gtalk) values(233,'first@first.com','insert_query') on duplicate KEY UPDATE email='test@test.com',gtalk='update_query'; -- when you execute the insert query for the first time, it will insert the values first@first.com and insert_query -- Next time when you execute it will update the same row with the values test@test.com and update_query |
This above query is not only going to save your query typing time, but your server is gonna love this very much. I am not talking about the query in terms of hundreds. Instead imagine the way is it going to take off the overhead from your server when you have thousands of query executing at the same time. So its a wise decision to use on DUPLICATE KEY where you can 🙂
Hope this helps you and your server 🙂
Cheers!!
Realin !
