Never been to TextSnippets before?

Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world (or not, you can keep them private!)

INSERT... ON DUPLICATE KEY UPDATE... (See related posts)

Please note: "INSERT... ON DUPLICATE KEY UPDATE..." can also use a compound (unique) key to check for duplication. This is very userful. For example:
If you have a log table to log hits to different websites daily, with "site_id"-s and "time" fields, where neither of them are primary keys, but togethether they are unique, then you can create a key on them, and then use "...ON DUPLICATE KEY..."

Table logs:
id: INT(11) auto_increment primary key
site_id: INT(11)
time: DATE
hits: INT(11)

Then:
CREATE UNIQUE INDEX comp ON logs (`site_id`, `time`);


And then you can:
INSERT INTO logs (`site_id`, `time`,`hits`) VALUES (1,"2004-08-09", 15) ON DUPLICATE KEY UPDATE hits=hits+15;

Excellent feature, and it is much faster and briefer then using first a select, then issuing either an update or an insert depending on the value of the select. You also get rid of the probably necessary table-lock during this action.

from: http://dev.mysql.com/doc/refman/5.0/en/insert.html
to sql by LMZ on Mon Apr 09 21:44:37 +0000 2007

Comments on this post

jamiew posts on Apr 11, 2007 at 14:31
This is an excellent tip, thanks. Do you know what versions of mysql this was introduced, or has it always been there?
LMZ posts on Apr 14, 2007 at 20:14
see link at the bottom of topic: "http://dev.mysql.com/doc/refman/5.0/en/insert.html" you see 5.0 in link? any more questions :)
jamiew posts on Apr 14, 2007 at 21:40
It is also documented at: http://dev.mysql.com/doc/refman/4.1/en/insert.html

So it is available down to MySQL 3.23

You need to create an account or log in to post comments to this site.


Related Posts