List:General Discussion« Previous MessageNext Message »
From:Andre Polykanine Date:February 14 2011 8:39pm
Subject:Re: Insert only if the entry doesn't exist
View as plain text  
Hello Rolando,

Sorry,  but  if  I  do  INSERT IGNORE INTO, then I must indicate a key
(typically a unique key or a primary key), or is it false? But I don't
know that key and no way to get it without more queries...

-- 
With best regards from Ukraine,
Andre
Skype: Francophile
My blog: http://oire.org/menelion (mostly in Russian)
Twitter: http://twitter.com/m_elensule
Facebook: http://facebook.com/menelion

------------ Original message ------------
From: Rolando Edwards <redwards@stripped>
To: João Cândido de Souza Neto
Date created: , 10:33:05 PM
Subject: Insert only if the entry doesn't exist


      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


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

Thread
Insert only if the entry doesn't existAndre Polykanine14 Feb
  • Re: Insert only if the entry doesn't existjoao14 Feb
    • RE: Insert only if the entry doesn't existRolando Edwards14 Feb
      • Re: Insert only if the entry doesn't existAndre Polykanine14 Feb
        • RE: Insert only if the entry doesn't existRolando Edwards14 Feb
          • Re: Insert only if the entry doesn't existAndre Polykanine15 Feb
            • Re: Insert only if the entry doesn't existJoerg Bruehe15 Feb