Re: REPLACE query
Kevin Carlson [EMAIL PROTECTED] wrote:
I have a table with four columns, the first three of which are combined
into a unique key:
create table Test {
cid int(9) NOT NULL default '0',
sid int(9) NOT NULL default '0',
uid int(9) NOT NULL default '0',
rating tinyint(1) NOT NULL default '0',
UNIQUE KEY csu1 (cid,sid,uid),
KEY cid1 (sid),
KEY sid1 (sid),
KEY uid1 (sid),
} TYPE=InnoDB;
I am using a REPLACE query to insert a row if it doesn't exist and
replace an existing row if one does exist:
REPLACE into TEST (cid, sid, uid, rating) values (580, 0, 205, 1)
In the case of this particular row, a row already exists with the
concatenated key of 580-0-205 and I am getting a duplicate key error. I
thought REPLACE was supposed to actually replace the contents of the row
if one exists. Does anyone have any ideas as to why this would be
causing a duplicate key error?
Works fine for me. The above CREATE TABLE statement has some syntax errors. What
exactly does CREATE TABLE look like?
What version of MySQL do you use?
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REPLACE query
I ran into this problem when I installed 4.0.18. All
of the tables in my database are INNODB and the
REPLACE statement was failing on tables that had
foreign key constraints. I just rolled back to 4.0.16
and the problems went away. Not much of a solution,
but it's buying me a little time. Will I have to get
rid of all of the REPLACE INTO statements and replace
them with INSERT/UPDATE statements or is there some
configuration setting that needs to be changed to make
it work?
Cheers,
Tripp
--- Victoria Reznichenko
[EMAIL PROTECTED] wrote:
Kevin Carlson [EMAIL PROTECTED] wrote:
I have a table with four columns, the first three
of which are combined
into a unique key:
create table Test {
cid int(9) NOT NULL default '0',
sid int(9) NOT NULL default '0',
uid int(9) NOT NULL default '0',
rating tinyint(1) NOT NULL default '0',
UNIQUE KEY csu1 (cid,sid,uid),
KEY cid1 (sid),
KEY sid1 (sid),
KEY uid1 (sid),
} TYPE=InnoDB;
I am using a REPLACE query to insert a row if it
doesn't exist and
replace an existing row if one does exist:
REPLACE into TEST (cid, sid, uid, rating) values
(580, 0, 205, 1)
In the case of this particular row, a row already
exists with the
concatenated key of 580-0-205 and I am getting a
duplicate key error. I
thought REPLACE was supposed to actually replace
the contents of the row
if one exists. Does anyone have any ideas as to
why this would be
causing a duplicate key error?
Works fine for me. The above CREATE TABLE statement
has some syntax errors. What exactly does CREATE
TABLE look like?
What version of MySQL do you use?
--
For technical support contracts, goto
https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net
http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__
[EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
__
Do you Yahoo!?
Yahoo! Tax Center - File online by April 15th
http://taxes.yahoo.com/filing.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REPLACE query
Emmett Bishop [EMAIL PROTECTED] wrote:
I ran into this problem when I installed 4.0.18. All
of the tables in my database are INNODB and the
REPLACE statement was failing on tables that had
foreign key constraints. I just rolled back to 4.0.16
and the problems went away. Not much of a solution,
but it's buying me a little time. Will I have to get
rid of all of the REPLACE INTO statements and replace
them with INSERT/UPDATE statements or is there some
configuration setting that needs to be changed to make
it work?
Could you provide a test case?
--- Victoria Reznichenko
[EMAIL PROTECTED] wrote:
Kevin Carlson [EMAIL PROTECTED] wrote:
I have a table with four columns, the first three
of which are combined
into a unique key:
create table Test {
cid int(9) NOT NULL default '0',
sid int(9) NOT NULL default '0',
uid int(9) NOT NULL default '0',
rating tinyint(1) NOT NULL default '0',
UNIQUE KEY csu1 (cid,sid,uid),
KEY cid1 (sid),
KEY sid1 (sid),
KEY uid1 (sid),
} TYPE=InnoDB;
I am using a REPLACE query to insert a row if it
doesn't exist and
replace an existing row if one does exist:
REPLACE into TEST (cid, sid, uid, rating) values
(580, 0, 205, 1)
In the case of this particular row, a row already
exists with the
concatenated key of 580-0-205 and I am getting a
duplicate key error. I
thought REPLACE was supposed to actually replace
the contents of the row
if one exists. Does anyone have any ideas as to
why this would be
causing a duplicate key error?
Works fine for me. The above CREATE TABLE statement
has some syntax errors. What exactly does CREATE
TABLE look like?
What version of MySQL do you use?
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
REPLACE query
I have a table with four columns, the first three of which are combined
into a unique key:
create table Test {
cid int(9) NOT NULL default '0',
sid int(9) NOT NULL default '0',
uid int(9) NOT NULL default '0',
rating tinyint(1) NOT NULL default '0',
UNIQUE KEY csu1 (cid,sid,uid),
KEY cid1 (sid),
KEY sid1 (sid),
KEY uid1 (sid),
} TYPE=InnoDB;
I am using a REPLACE query to insert a row if it doesn't exist and
replace an existing row if one does exist:
REPLACE into TEST (cid, sid, uid, rating) values (580, 0, 205, 1)
In the case of this particular row, a row already exists with the
concatenated key of 580-0-205 and I am getting a duplicate key error. I
thought REPLACE was supposed to actually replace the contents of the row
if one exists. Does anyone have any ideas as to why this would be
causing a duplicate key error?
Thanks,
Kevin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replace query
I have a MySQL database with records in a table that i would like to replace. The table is items and the field is description. I would like to replace the existing text within this filed with some new text. I did this before and it was successful but I have lost the query to do it :( Could someone shed some light in this please :) Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replace query
On Monday 17 November 2003 18.04, Andrew wrote: I have a MySQL database with records in a table that i would like to replace. The table is items and the field is description. I would like to replace the existing text within this filed with some new text. I did this before and it was successful but I have lost the query to do it :( Could someone shed some light in this please :) Andrew http://www.mysql.com/doc/en/Data_Manipulation.html REPLACE items SET description=RTFM WHERE ...(insert your where clause here) Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: replace query + RTFM?
Hi Mike I really appreciate you getting back to me so quickly :) Can I just clarify this becuse I have over 4000 records and not all of the records have anything in items - description but there are about 2500 records with this exact text- br /A-Z Business Directory br /br / Please call 0116 27 960 41 for further details br /br / Replace these details with yours simply by calling 0116 27 960 41 br /br / Unlike YELL we only list a maximum of 5 businesses per Business Category br /br / The chances of your business being called has been increased already. Be Seen by calling 0116 27 960 41 or br /br /A HREF=http://www.punterspower.co.uk/register_online.php;registering online/Abr /br /ulliNo Web Site needed/liliNo Internet knowledge required/liliAll administration carried out by us/liliAll contacts forwarded to you/liliComprehensive Company details/liliLink to your own web site/liliYou can add html within your description making your profile suit/liliLogin access to administer and change you details whenever you need to/li/ul and it just the telephone I want to change :) so is the correct query REPLACE items SET description=new telphone number WHERE (old telephone number) Thank you so much Andrew PS what does RTFM mean? -Original Message- From: Mikael Fridh [mailto:[EMAIL PROTECTED] Sent: 17 November 2003 17:12 To: [EMAIL PROTECTED]; MySQL-Lista Subject: Re: replace query On Monday 17 November 2003 18.04, Andrew wrote: I have a MySQL database with records in a table that i would like to replace. The table is items and the field is description. I would like to replace the existing text within this filed with some new text. I did this before and it was successful but I have lost the query to do it :( Could someone shed some light in this please :) Andrew http://www.mysql.com/doc/en/Data_Manipulation.html REPLACE items SET description=RTFM WHERE ...(insert your where clause here) Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.541 / Virus Database: 335 - Release Date: 14/11/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: replace query + RTFM?
amazing, and you guys are errr intelligent!!! can't actually help but you can make an abbreviation for RTFM how fucking sad is that, what a bunch of pathetic losers. -Original Message- From: Rodolphe Toots [mailto:[EMAIL PROTECTED] Sent: 17 November 2003 18:11 To: [EMAIL PROTECTED] Subject: SV: replace query + RTFM? RTFM = read the fucking manual.. -Ursprungligt meddelande- Från: Andrew [mailto:[EMAIL PROTECTED] Skickat: den 17 november 2003 18:55 Till: Mikael Fridh; [EMAIL PROTECTED]; MySQL-Lista Ämne: RE: replace query + RTFM? Hi Mike I really appreciate you getting back to me so quickly :) Can I just clarify this becuse I have over 4000 records and not all of the records have anything in items - description but there are about 2500 records with this exact text- br /A-Z Business Directory br /br / Please call 0116 27 960 41 for further details br /br / Replace these details with yours simply by calling 0116 27 960 41 br /br / Unlike YELL we only list a maximum of 5 businesses per Business Category br /br / The chances of your business being called has been increased already. Be Seen by calling 0116 27 960 41 or br /br /A HREF=http://www.punterspower.co.uk/register_online.php;registering online/Abr /br /ulliNo Web Site needed/liliNo Internet knowledge required/liliAll administration carried out by us/liliAll contacts forwarded to you/liliComprehensive Company details/liliLink to your own web site/liliYou can add html within your description making your profile suit/liliLogin access to administer and change you details whenever you need to/li/ul and it just the telephone I want to change :) so is the correct query REPLACE items SET description=new telphone number WHERE (old telephone number) Thank you so much Andrew PS what does RTFM mean? -Original Message- From: Mikael Fridh [mailto:[EMAIL PROTECTED] Sent: 17 November 2003 17:12 To: [EMAIL PROTECTED]; MySQL-Lista Subject: Re: replace query On Monday 17 November 2003 18.04, Andrew wrote: I have a MySQL database with records in a table that i would like to replace. The table is items and the field is description. I would like to replace the existing text within this filed with some new text. I did this before and it was successful but I have lost the query to do it :( Could someone shed some light in this please :) Andrew http://www.mysql.com/doc/en/Data_Manipulation.html REPLACE items SET description=RTFM WHERE ...(insert your where clause here) Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.541 / Virus Database: 335 - Release Date: 14/11/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.541 / Virus Database: 335 - Release Date: 14/11/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: replace query + RTFM?
Andrew, Please allow a small amount of grief to come from these lists... People are usually glad to help, and Mike sent the info that he knew to you. There is a certain amount of frustration that develops when people repeatedly ask questions of the mailing list that are clearly documented in the mysql documentation. Suggesting to read the manual is not a bad thing, and RTFM is a long stading acronym that nobody on this list invented, and I guarentee everyone on this list is guilty of asking a question that is in the documentation for some product / project they were working on. The MySQL documentation is, IMHO (in my humble opinion - another oldie acronym), incredibly good for a free product. Searching it for answers, particularly about syntax, should be everyone's, including my, first step in solving an issue that we're having. [getting down off of soap box] Dan Greene -Original Message- From: Andrew [mailto:[EMAIL PROTECTED] Sent: Monday, November 17, 2003 1:16 PM To: MySQL-Lista; Rodolphe Toots Subject: RE: replace query + RTFM? amazing, and you guys are errr intelligent!!! can't actually help but you can make an abbreviation for RTFM how f#$#$ng sad is that, what a bunch of pathetic losers. -Original Message- From: Rodolphe Toots [mailto:[EMAIL PROTECTED] Sent: 17 November 2003 18:11 To: [EMAIL PROTECTED] Subject: SV: replace query + RTFM? RTFM = read the fing manual.. -Ursprungligt meddelande- Från: Andrew [mailto:[EMAIL PROTECTED] Skickat: den 17 november 2003 18:55 Till: Mikael Fridh; [EMAIL PROTECTED]; MySQL-Lista Ämne: RE: replace query + RTFM? Hi Mike I really appreciate you getting back to me so quickly :) Can I just clarify this becuse I have over 4000 records and not all of the records have anything in items - description but there are about 2500 records with this exact text- br /A-Z Business Directory br /br / Please call 0116 27 960 41 for further details br /br / Replace these details with yours simply by calling 0116 27 960 41 br /br / Unlike YELL we only list a maximum of 5 businesses per Business Category br /br / The chances of your business being called has been increased already. Be Seen by calling 0116 27 960 41 or br /br /A HREF=http://www.punterspower.co.uk/register_online.php;registering online/Abr /br /ulliNo Web Site needed/liliNo Internet knowledge required/liliAll administration carried out by us/liliAll contacts forwarded to you/liliComprehensive Company details/liliLink to your own web site/liliYou can add html within your description making your profile suit/liliLogin access to administer and change you details whenever you need to/li/ul and it just the telephone I want to change :) so is the correct query REPLACE items SET description=new telphone number WHERE (old telephone number) Thank you so much Andrew PS what does RTFM mean? -Original Message- From: Mikael Fridh [mailto:[EMAIL PROTECTED] Sent: 17 November 2003 17:12 To: [EMAIL PROTECTED]; MySQL-Lista Subject: Re: replace query On Monday 17 November 2003 18.04, Andrew wrote: I have a MySQL database with records in a table that i would like to replace. The table is items and the field is description. I would like to replace the existing text within this filed with some new text. I did this before and it was successful but I have lost the query to do it :( Could someone shed some light in this please :) Andrew http://www.mysql.com/doc/en/Data_Manipulation.html REPLACE items SET description=RTFM WHERE ...(insert your where clause here) Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.541 / Virus Database: 335 - Release Date: 14/11/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.541 / Virus Database: 335 - Release Date: 14/11/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: replace query + RTFM?
I understand what the manual is saying and I was asking for confirmation that what I was doing was indeed the right way forward, instead of going a head and potentially fucking up 2500 records! But alas it seems that people are repared to spend more time writing shite about reading manuals and reading manuals and how to read manual and acronymn and reading manual and areading manuals and reading manual and areading manuals and reading manual and areading manuals and reading manual and areading manuals and reading manual and areading manuals and reading manual and areading manuals and reading manual and areading manuals and reading manual and areading manualsand reading manual and areading manuals must remind myself I am human one day! -Original Message- From: Dan Greene [mailto:[EMAIL PROTECTED] Sent: 17 November 2003 18:31 To: [EMAIL PROTECTED]; MySQL-Lista; Rodolphe Toots Subject: RE: replace query + RTFM? Andrew, Please allow a small amount of grief to come from these lists... People are usually glad to help, and Mike sent the info that he knew to you. There is a certain amount of frustration that develops when people repeatedly ask questions of the mailing list that are clearly documented in the mysql documentation. Suggesting to read the manual is not a bad thing, and RTFM is a long stading acronym that nobody on this list invented, and I guarentee everyone on this list is guilty of asking a question that is in the documentation for some product / project they were working on. The MySQL documentation is, IMHO (in my humble opinion - another oldie acronym), incredibly good for a free product. Searching it for answers, particularly about syntax, should be everyone's, including my, first step in solving an issue that we're having. [getting down off of soap box] Dan Greene -Original Message- From: Andrew [mailto:[EMAIL PROTECTED] Sent: Monday, November 17, 2003 1:16 PM To: MySQL-Lista; Rodolphe Toots Subject: RE: replace query + RTFM? amazing, and you guys are errr intelligent!!! can't actually help but you can make an abbreviation for RTFM how f#$#$ng sad is that, what a bunch of pathetic losers. -Original Message- From: Rodolphe Toots [mailto:[EMAIL PROTECTED] Sent: 17 November 2003 18:11 To: [EMAIL PROTECTED] Subject: SV: replace query + RTFM? RTFM = read the fing manual.. -Ursprungligt meddelande- Från: Andrew [mailto:[EMAIL PROTECTED] Skickat: den 17 november 2003 18:55 Till: Mikael Fridh; [EMAIL PROTECTED]; MySQL-Lista Ämne: RE: replace query + RTFM? Hi Mike I really appreciate you getting back to me so quickly :) Can I just clarify this becuse I have over 4000 records and not all of the records have anything in items - description but there are about 2500 records with this exact text- br /A-Z Business Directory br /br / Please call 0116 27 960 41 for further details br /br / Replace these details with yours simply by calling 0116 27 960 41 br /br / Unlike YELL we only list a maximum of 5 businesses per Business Category br /br / The chances of your business being called has been increased already. Be Seen by calling 0116 27 960 41 or br /br /A HREF=http://www.punterspower.co.uk/register_online.php;registering online/Abr /br /ulliNo Web Site needed/liliNo Internet knowledge required/liliAll administration carried out by us/liliAll contacts forwarded to you/liliComprehensive Company details/liliLink to your own web site/liliYou can add html within your description making your profile suit/liliLogin access to administer and change you details whenever you need to/li/ul and it just the telephone I want to change :) so is the correct query REPLACE items SET description=new telphone number WHERE (old telephone number) Thank you so much Andrew PS what does RTFM mean? -Original Message- From: Mikael Fridh [mailto:[EMAIL PROTECTED] Sent: 17 November 2003 17:12 To: [EMAIL PROTECTED]; MySQL-Lista Subject: Re: replace query On Monday 17 November 2003 18.04, Andrew wrote: I have a MySQL database with records in a table that i would like to replace. The table is items and the field is description. I would like to replace the existing text within this filed with some new text. I did this before and it was successful but I have lost the query to do it :( Could someone shed some light in this please :) Andrew http://www.mysql.com/doc/en/Data_Manipulation.html REPLACE items SET description=RTFM WHERE ...(insert your where clause here) Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.541 / Virus Database: 335 - Release Date: 14/11/2003 -- MySQL General Mailing List For list archives: http
RE: replace query + RTFM?
At 6:39 PM + 11/17/03, Andrew wrote: I understand what the manual is saying and I was asking for confirmation that what I was doing was indeed the right way forward, instead of going a head and potentially fucking up 2500 records! But alas it seems that people are repared to spend more time writing shite about reading manuals and reading manuals and how to read manual and acronymn and reading manual and areading manuals and reading manual and areading manuals and reading manual and areading manuals and reading manual and areading manuals and reading manual and areading manuals and reading manual and areading manuals and reading manual and areading manuals and reading manual and areading manualsand reading manual and areading manuals must remind myself I am human one day! Hmm... As far as I can tell, this thread began with you saying that you had a query to do what you want, but that you lost it and can someone help? I think you'll have to admit that we are not mind-readers, so we cannot tell you what your original query was. In any case, I do not think that REPLACE is what you want, if what you want to do is replace only the description column value. REPLACE requires values for all the columns; if you omit certain columns, MySQL will set them to their default values, which doesn't seem like what you want. Looks to me like you want to use UPDATE instead. Specify a WHERE clause that identifies the rows you want to update, and in the SET clause specify the new value for the description column. To pre-flight your UPDATE, you can write a SELECT that has the same WHERE clause. This will show you whether or not you are indeed identifying the proper rows. -Original Message- From: Dan Greene [mailto:[EMAIL PROTECTED] Sent: 17 November 2003 18:31 To: [EMAIL PROTECTED]; MySQL-Lista; Rodolphe Toots Subject: RE: replace query + RTFM? Andrew, Please allow a small amount of grief to come from these lists... People are usually glad to help, and Mike sent the info that he knew to you. There is a certain amount of frustration that develops when people repeatedly ask questions of the mailing list that are clearly documented in the mysql documentation. Suggesting to read the manual is not a bad thing, and RTFM is a long stading acronym that nobody on this list invented, and I guarentee everyone on this list is guilty of asking a question that is in the documentation for some product / project they were working on. The MySQL documentation is, IMHO (in my humble opinion - another oldie acronym), incredibly good for a free product. Searching it for answers, particularly about syntax, should be everyone's, including my, first step in solving an issue that we're having. [getting down off of soap box] Dan Greene -Original Message- From: Andrew [mailto:[EMAIL PROTECTED] Sent: Monday, November 17, 2003 1:16 PM To: MySQL-Lista; Rodolphe Toots Subject: RE: replace query + RTFM? amazing, and you guys are errr intelligent!!! can't actually help but you can make an abbreviation for RTFM how f#$#$ng sad is that, what a bunch of pathetic losers. -Original Message- From: Rodolphe Toots [mailto:[EMAIL PROTECTED] Sent: 17 November 2003 18:11 To: [EMAIL PROTECTED] Subject: SV: replace query + RTFM? RTFM = read the fing manual.. -Ursprungligt meddelande- Från: Andrew [mailto:[EMAIL PROTECTED] Skickat: den 17 november 2003 18:55 Till: Mikael Fridh; [EMAIL PROTECTED]; MySQL-Lista Ämne: RE: replace query + RTFM? Hi Mike I really appreciate you getting back to me so quickly :) Can I just clarify this becuse I have over 4000 records and not all of the records have anything in items - description but there are about 2500 records with this exact text- br /A-Z Business Directory br /br / Please call 0116 27 960 41 for further details br /br / Replace these details with yours simply by calling 0116 27 960 41 br /br / Unlike YELL we only list a maximum of 5 businesses per Business Category br /br / The chances of your business being called has been increased already. Be Seen by calling 0116 27 960 41 or br /br /A HREF=http://www.punterspower.co.uk/register_online.php;registering online/Abr /br /ulliNo Web Site needed/liliNo Internet knowledge required/liliAll administration carried out by us/liliAll contacts forwarded to you/liliComprehensive Company details/liliLink to your own web site/liliYou can add html within your description making your profile suit/liliLogin access to administer and change you details whenever you need to/li/ul and it just the telephone I want to change :) so is the correct query REPLACE items SET description=new telphone number WHERE (old telephone number) Thank you so much Andrew PS what does RTFM mean? -Original Message- From: Mikael Fridh [mailto:[EMAIL PROTECTED] Sent: 17 November 2003 17:12 To: [EMAIL PROTECTED]; MySQL-Lista Subject: Re: replace query
RE: replace query + RTFM?
Looks to me like you want to use UPDATE instead. Specify a WHERE clause that identifies the rows you want to update, and in the SET clause specify the new value for the description column. To pre-flight your UPDATE, you can write a SELECT that has the same WHERE clause. This will show you whether or not you are indeed identifying the proper rows. thank you paul Indeed I think you are right and if memory serves me correct you supplied my solution the first time ;) I thought I had saved the query for the next time I needed it! As it happens I can't find it. I'll have a read on the UPDATE and see if I can work it out. Thank you Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: replace query + RTFM?
Can someone kindly tell me what I doing wrong and help with this query? I want to replace / update the text for every record within table items field ItemDescription. This is what I have so far and its isn't corroect :( UPDATE items SET ItemDescription='A-Z Business Directory br /br / Please call 0870 199 4080 for further details br /br / Replace these details simply by calling 0870 199 4080 br /br / Unlike YELL we only list a maximum of 5 businesses per Business Category br /br / The chances of your business being called has been increased already. Be Seen by calling 0870 199 4080' WHERE 'A-Z Business Directory br /br / Please call 0116 27 960 41 for further details br /br / Replace these details simply by calling 0116 27 960 41 br /br / Unlike YELL we only list a maximum of 5 businesses per Business Category br /br / The chances of your business being called has been increased already. Be Seen by calling 0116 27 960 41' Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: replace query + RTFM?
you need to tell mysql what field to equate to the value in your where clause: update items set ItemDescription = 'new text' where ItemDescription = 'old text' replacing the items with the text you have below -Original Message- From: Andrew [mailto:[EMAIL PROTECTED] Sent: Monday, November 17, 2003 4:19 PM To: [EMAIL PROTECTED]; Mikael Fridh; MySQL-Lista Subject: RE: replace query + RTFM? Can someone kindly tell me what I doing wrong and help with this query? I want to replace / update the text for every record within table items field ItemDescription. This is what I have so far and its isn't corroect :( UPDATE items SET ItemDescription='A-Z Business Directory br /br / Please call 0870 199 4080 for further details br /br / Replace these details simply by calling 0870 199 4080 br /br / Unlike YELL we only list a maximum of 5 businesses per Business Category br /br / The chances of your business being called has been increased already. Be Seen by calling 0870 199 4080' WHERE 'A-Z Business Directory br /br / Please call 0116 27 960 41 for further details br /br / Replace these details simply by calling 0116 27 960 41 br /br / Unlike YELL we only list a maximum of 5 businesses per Business Category br /br / The chances of your business being called has been increased already. Be Seen by calling 0116 27 960 41' Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replace query + RTFM?
On Monday 17 November 2003 01:18 pm, Andrew wrote: Can someone kindly tell me what I doing wrong and help with this query? I want to replace / update the text for every record within table items field ItemDescription. This is what I have so far and its isn't corroect :( UPDATE items SET ItemDescription='A-Z Business Directory br /br / Please call 0870 199 4080 for further details br /br / Replace these details simply by calling 0870 199 4080 br /br / Unlike YELL we only list a maximum of 5 businesses per Business Category br /br / The chances of your business being called has been increased already. Be Seen by calling 0870 199 4080' WHERE ItemDescription= 'A-Z Business Directory br /br / Please call 0116 27 960 41 for further details br /br / Replace these details simply by calling 0116 27 960 41 br /br / Unlike YELL we only list a maximum of 5 businesses per Business Category br /br / The chances of your business being called has been increased already. Be Seen by calling 0116 27 960 41' should do it. gabe. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: replace query + RTFM?
Worked like a dream Dan Thanks Paul for being patient :) Thank you Andrew -Original Message- From: Dan Greene [mailto:[EMAIL PROTECTED] Sent: 17 November 2003 21:27 To: [EMAIL PROTECTED]; Mikael Fridh; MySQL-Lista Subject: RE: replace query + RTFM? you need to tell mysql what field to equate to the value in your where clause: update items set ItemDescription = 'new text' where ItemDescription = 'old text' replacing the items with the text you have below -Original Message- From: Andrew [mailto:[EMAIL PROTECTED] Sent: Monday, November 17, 2003 4:19 PM To: [EMAIL PROTECTED]; Mikael Fridh; MySQL-Lista Subject: RE: replace query + RTFM? Can someone kindly tell me what I doing wrong and help with this query? I want to replace / update the text for every record within table items field ItemDescription. This is what I have so far and its isn't corroect :( UPDATE items SET ItemDescription='A-Z Business Directory br /br / Please call 0870 199 4080 for further details br /br / Replace these details simply by calling 0870 199 4080 br /br / Unlike YELL we only list a maximum of 5 businesses per Business Category br /br / The chances of your business being called has been increased already. Be Seen by calling 0870 199 4080' WHERE 'A-Z Business Directory br /br / Please call 0116 27 960 41 for further details br /br / Replace these details simply by calling 0116 27 960 41 br /br / Unlike YELL we only list a maximum of 5 businesses per Business Category br /br / The chances of your business being called has been increased already. Be Seen by calling 0116 27 960 41' Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.541 / Virus Database: 335 - Release Date: 14/11/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Replace query question
speters, Thursday, September 19, 2002, 10:55:41 PM, you wrote: samdc If i use a replace query, and there isn't an error, then either samdc a new row was inserted, or an existing row was updated. samdc The primary key in the table i'm replacing to is defined as samdc mprid int not null auto_increment primary key samdc whether an insert or update actually occurs, i want to get the value of samdc mprid that was affected. samdc if it's a new row, i assume i can use select last_insert_id() samdc but if its an update, will that work? LAST_INSERT_ID() will return you correct value only if you insert NULL or 0 into auto_increment field: http://www.mysql.com/doc/en/Miscellaneous_functions.html If you insert NULL or 0 why do you use REPLACE? If you insert any other values, you should know what you inserted, shouldn't you? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replace query question
If i use a replace query, and there isn't an error, then either a new row was inserted, or an existing row was updated. The primary key in the table i'm replacing to is defined as mprid int not null auto_increment primary key whether an insert or update actually occurs, i want to get the value of mprid that was affected. if it's a new row, i assume i can use select last_insert_id() but if its an update, will that work? thanks, sean peters [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
replace query
Hi, i have a table with 2000 rows. One column (name answer)has a string (ok --) in all the rows.(e.g ok -- yes we support this.). now i want to remove all these 'ok --' in this column..leaving(e.g yes we support this) I tried to do it with the replace query but being a newbie to mysql i couldnt find the right syntax. Can someone help. thanx and regards anil - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: replace query
On Wednesday, 10. July 2002 18:13, Anil Garg wrote: Hi! I tried to do it with the replace query but being a newbie to mysql i couldnt find the right syntax. Can someone help. http://www.mysql.com/doc/U/P/UPDATE.html Regards Georg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: replace query
On 10 Jul 2002, at 12:13, Anil Garg wrote: One column (name answer)has a string (ok --) in all the rows.(e.g ok -- yes we support this.). now i want to remove all these 'ok --' in this column..leaving(e.g yes we support this) I tried to do it with the replace query but being a newbie to mysql i couldnt find the right syntax. You don't want a REPLACE *query* -- you want to use an UPDATE query with the REPLACE *function*. The two have nothing to do with each other. Have you looked at the documentation? http://www.mysql.com/doc/U/P/UPDATE.html http://www.mysql.com/doc/S/t/String_functions.html So you seem to want something like UPDATE table_name SET answer = REPLACE(answer, 'ok -- ', ''); -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
