Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

wwwxxx,nvif, why atm producer might be held liable for economic injury, wwwxxxy=ServiceLogin, w2ksp4.exe, WwwxxXdbf, procmail "FROM_MAILER" patch, Use of assignment to $[ is deprecated at /usr/local/sbin/apxs line 86. , wwwxxx vim, mysql closing table and opening table, 800c5000

Links

XODOX
Impressum

#1: Insert only if the entry doesn"t exist

Posted on 2011-02-14 19:12:16 by Andre Polykanine

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`=3D'12345' AND `UserId`=3D'789'";
then I'm checking if the user has already clicked a link:
if (mysql_num_rows()==0) {
"INSERT INTO `Votes` SET `EntryId`=3D'12345', `UserId`=3D'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!



--=20
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=3Dgcdmg-mysql-2@m.gmane.o rg

Report this message

#2: Re: Insert only if the entry doesn"t exist

Posted on 2011-02-14 19:38:40 by joao

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

--
João Cândido de Souza Neto

"Andre Polykanine" <andre@oire.org> escreveu na mensagem
news:1621362474.20110214201216@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=gcdmg-mysql-2@m.gmane.org

Report this message

#3: RE: Insert only if the entry doesn"t exist

Posted on 2011-02-14 21:33:05 by 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@logicworks.net
http://www.linkedin.com/in/rolandoedwards


-----Original Message-----
From: Jo=E3o C=E2ndido de Souza Neto [mailto:joao@consultorweb.cnt.br]=20
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".

--=20
Jo=E3o C=E2ndido de Souza Neto

"Andre Polykanine" <andre@oire.org> escreveu na mensagem=20
news:1621362474.20110214201216@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`=3D'12345' AND `UserId`=3D'789'";
then I'm checking if the user has already clicked a link:
if (mysql_num_rows()==0) {
"INSERT INTO `Votes` SET `EntryId`=3D'12345', `UserId`=3D'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!



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



--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dredwards@logicworks=
..net


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Report this message

#4: Re: Insert only if the entry doesn"t exist

Posted on 2011-02-14 21:39:38 by Andre Polykanine

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...

--=20
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@logicworks.net>
To: Jo=E3o C=E2ndido 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 my=
sqld'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@logicworks.net
http://www.linkedin.com/in/rolandoedwards


-----Original Message-----
From: Jo=E3o C=E2ndido de Souza Neto [mailto:joao@consultorweb.cnt.br]=20
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".

--=20
Jo=E3o C=E2ndido de Souza Neto

"Andre Polykanine" <andre@oire.org> escreveu na mensagem=20
news:1621362474.20110214201216@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`=3D'12345' AND `UserId`=3D'789'";
then I'm checking if the user has already clicked a link:
if (mysql_num_rows()==0) {
"INSERT INTO `Votes` SET `EntryId`=3D'12345', `UserId`=3D'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!



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



--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dredwards@logicworks=
..net


--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dandre@oire.org


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Report this message

#5: RE: Insert only if the entry doesn"t exist

Posted on 2011-02-14 22:01:40 by Rolando Edwards

If the table has Primary and/or UNIQUE Keys, then you are fine.

You do not need to know what they are. If you want to see them do this:
SHOW CREATE TABLE Votes\G

INSERT IGNORE INTO does not require ON DUPLICATE KEY options.
After all, you said earlier that you want to do nothing if the row exists.

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@logicworks.net
http://www.linkedin.com/in/rolandoedwards


-----Original Message-----
From: Andre Polykanine [mailto:andre@oire.org]=20
Sent: Monday, February 14, 2011 3:40 PM
To: Rolando Edwards
Cc: Jo=E3o C=E2ndido de Souza Neto; mysql@lists.mysql.com
Subject: Re: Insert only if the entry doesn't exist

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...

--=20
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@logicworks.net>
To: Jo=E3o C=E2ndido 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 my=
sqld'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@logicworks.net
http://www.linkedin.com/in/rolandoedwards


-----Original Message-----
From: Jo=E3o C=E2ndido de Souza Neto [mailto:joao@consultorweb.cnt.br]=20
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".

--=20
Jo=E3o C=E2ndido de Souza Neto

"Andre Polykanine" <andre@oire.org> escreveu na mensagem=20
news:1621362474.20110214201216@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`=3D'12345' AND `UserId`=3D'789'";
then I'm checking if the user has already clicked a link:
if (mysql_num_rows()==0) {
"INSERT INTO `Votes` SET `EntryId`=3D'12345', `UserId`=3D'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!



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



--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dredwards@logicworks=
..net


--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dandre@oire.org


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Report this message

#6: Re: Insert only if the entry doesn"t exist

Posted on 2011-02-15 01:14:57 by Andre Polykanine

Hello Rolando,

So if I do
"INSERT IGNORE INTO `Votes` SET `EntryId`=3D'12345', UserId`=3D'789'";
it *won't* insert the second row if there's a row with EntryId set to
12345 and UserId set to 789?
Thanks and sorry!)

--=20
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@logicworks.net>
To: Andre Polykanine
Date created: , 11:01:40 PM
Subject: Insert only if the entry doesn't exist


If the table has Primary and/or UNIQUE Keys, then you are fine.

You do not need to know what they are. If you want to see them do this:
SHOW CREATE TABLE Votes\G

INSERT IGNORE INTO does not require ON DUPLICATE KEY options.
After all, you said earlier that you want to do nothing if the row exists.

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@logicworks.net
http://www.linkedin.com/in/rolandoedwards


-----Original Message-----
From: Andre Polykanine [mailto:andre@oire.org]=20
Sent: Monday, February 14, 2011 3:40 PM
To: Rolando Edwards
Cc: Jo=E3o C=E2ndido de Souza Neto; mysql@lists.mysql.com
Subject: Re: Insert only if the entry doesn't exist

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...

--=20
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@logicworks.net>
To: Jo=E3o C=E2ndido 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 my=
sqld'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@logicworks.net
http://www.linkedin.com/in/rolandoedwards


-----Original Message-----
From: Jo=E3o C=E2ndido de Souza Neto [mailto:joao@consultorweb.cnt.br]=20
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".

--=20
Jo=E3o C=E2ndido de Souza Neto

"Andre Polykanine" <andre@oire.org> escreveu na mensagem=20
news:1621362474.20110214201216@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`=3D'12345' AND `UserId`=3D'789'";
then I'm checking if the user has already clicked a link:
if (mysql_num_rows()==0) {
"INSERT INTO `Votes` SET `EntryId`=3D'12345', `UserId`=3D'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!



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



--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dredwards@logicworks=
..net


--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dandre@oire.org


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Report this message

#7: Re: Insert only if the entry doesn"t exist

Posted on 2011-02-15 19:58:40 by Joerg Bruehe

Hi!


Andre Polykanine wrote:
> Hello Rolando,
>=20
> So if I do
> "INSERT IGNORE INTO `Votes` SET `EntryId`=3D'12345', UserId`=3D'789'";
> it *won't* insert the second row if there's a row with EntryId set to
> 12345 and UserId set to 789?

If you want to have at most one vote per user on any entry, IMO you
should have a "unique index" on that combination of fields.


Aside:
Assuming you "*Id" values are numeric types, you should stop using
quotes around them: quotes enclose strings.
I know MySQL implicitly converts numbers to strings and vice versa, but
this costs performance and IMO is a bad idea anyways.
I feel a programmer should know the distinction of numeric and string
types and keep them separate in a well-structured application.


HTH,
J=F6rg

--=20
Joerg Bruehe, MySQL Build Team, joerg.bruehe@oracle.com
ORACLE Deutschland B.V. & Co. KG, Komturstrasse 18a, D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Report this message