A good practice of increasing SQL efficiency is to reduce the number of separate queries as much as possible. In a situation where you want a row updated if it exists and inserted if it doesn’t may take a newbie MySQL developer to write 2 independent queries, namely:
- first, check if the row exists with “SELECT * FROM table WHERE …”
- second, if it exists, “UPDATE table SET …”; ir it doesn’t exist, “INSERT INTO table …”
thus loading the server with unnecessary burdens. Checking if a certain record already exists and decide between insert or update can be simply achieved by a single MySQL query:
REPLACE INTO table SET id = 42, foo = 'bar';REPLACE INTO first checks if the row with id = 42 already exists, if it does, it deletes the row and insert the current one; if it doesn’t, it simply inserts the current record.
Another way is to use ON DUPLICATE KEY UPDATE clause in a paradigm such as INSERT … ON DUPLICATE KEY UPDATE to determine if the record to be inserted can cause a duplicate entry in a UNIQUE index or PRIMARY KEY index, if it doesn’t, it inserts it, if it does, the existing row is updated. The following two statements have the same results:
INSERT INTO table (a, b, c) VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE c = c + 1;
// column ‘a’ is UNIQUE indexed, thus to prevent duplicate UNIQUE column values, the old row (whose a = 1) is updated with a new value of ‘c’UPDATE table SET c = c + 1 WHERE a = 1;
Facebook
Twitter
Google Plus
{ 11 comments… read them below or add one }
Thanks for the article. It was exactly what I was looking for. You saved me a lot of time. I hope I can return the favor back to the community of developers soon. : )
Cheers for the article. Always find the MySQL official documentation a bit heavy to read and understand. Could understand this though :)
X2 – the MySQL documentation gets quite confusing sometimes
how can I check for more than one field?
I would link to insert the field in case that 3 of its columns don’t exist.
INSERT INTO TABLE (id,ip,date,rate) VALUES (NULL,’111.333.222.444′,now(),’3′)
However, I’m checking not only for the Primary Key (which is an auto-increment anyways), but for fields ‘ip’ and ‘date’.
I need for an IP to be able to rate once per day.
Thanks!
Thx dude, awesome, it really help me.
Jo, you can create one more filed, with a md5key with the date and the ip.
example on php:
$ip = $_SERVER['remote_addr'];
$today = date(“Y-m-d”);
$key = md5($ip.”:”.$today);
and the example:
REPLACE INTO table SET md5key= $key, foo = ‘bar’;
or
INSERT INTO table (a, b, c) VALUES ($key, 2, 3) ON DUPLICATE KEY UPDATE c = c + 1;
Thank you so much .. you really saved my day since I was trying to avoid using SELECT, IF, UPDATE, INSERT query.
Thanks again!
Bingo bango! Thanks..
Thank you!
nice way to do it, but it doesn’t work when the id is present as a foreing key in another table. There’s an error of restriction or even worst, a cascade elimination of registers in the foreing table. Take care of this point before try this command. Bye!
Thanks you so much. That’s work great. Wonderful post and explanation.
{ 1 trackback }