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 redwa...@logicworks.net http://www.linkedin.com/in/rolandoedwards -----Original Message----- From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br] Sent: Monday, February 14, 2011 1:39 PM To: mysql@lists.mysql.com 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" <an...@oire.org> escreveu na mensagem news:1621362474.20110214201...@oire.org... 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=redwa...@logicworks.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
