RE: Insert only if the entry doesn't exist

[Rolando Edwards]

Be Careful. REPLACE INTO mechanically does DELETE and INSERT under mysqld's hood.
If you want to do nothing if row exists already then do:

INSERT IGNORE instead of REPLACE INTO

Rolando A. Edwards
MySQL DBA (SCMDBA)

155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM & Skype : RolandoLogicWorx
redwards@stripped
http://www.linkedin.com/in/rolandoedwards


-----Original Message-----
From: João Cândido de Souza Neto [mailto:joao@stripped] 
Sent: Monday, February 14, 2011 1:39 PM
To: mysql@stripped
Subject: Re: Insert only if the entry doesn't exist

Instead of "insert into" you can use "replace into".

-- 
João Cândido de Souza Neto

"Andre Polykanine" <andre@stripped> escreveu na mensagem 
news:1621362474.20110214201216@ style="color:#666">stripped...
Hi all,
Thanks for your fast answer to my last question!
Here's one more problem I commonly deal with.
There  are  cases  when  I need to insert the row only if such a row
doesn't  exist,  otherwise  I  need either to update the row or to do
nothing, just skip the query.
The  common case is the rating increment/decrement. I have the + and -
links  which are AJAX. So I need to prevent fast multiple clicking and
over-increasing or over-decreasing the rating of the blog entry.
Here's what I'm doing, in a simplified form (assume all of the numbers
are PHP variables):
"SELECT `Id` FROM `Votes` WHERE `EntryId`='12345' AND `UserId`='789'";
then I'm checking if the user has already clicked a link:
if (mysql_num_rows()==0) {
"INSERT INTO `Votes` SET `EntryId`='12345', `UserId`='789'";
} else {
// do nothing
}

And here is the question (at last!):
How  can  I  MySql'ly  check whether a vote does exist and if it does,
then insert the row, else either do nothing or update several columns?
I  assume I can't use ON DUPLICATE KEY UPDATE because I have no unique
keys...
Hope you understood my problem).
Thanks!



-- 
With best regards from Ukraine,
Andre
Skype: Francophile
Twitter: http://twitter.com/m_elensule
Facebook: http://facebook.com/menelion



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1