SQL command to find and replace a word in the products description?

Need help with a SQL command to find and replace one word in the products_description table, and set products_description to a new word, where products_description is this word..

I have the word hematite around 400 times in the product_description and want to change to magnatite

UPDATE products_description
SET products_description = 'magnatite'
WHERE products_description = 'hematite';

1. admin panel/ tools/ install sql patches
2. I insert the following and get 1 processed but no change
3. What is wrong... :lamo:

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

You want to use replace

yellow1912:

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

You want to use replace

Yellow1912,
SELECT REPLACE('hematite', 'hematite', 'magnatite');

1. I click send
2. 1 statement processed
3. But no change on the page.
4. What is wrong???

Select is ....select, that's it, it doesnt do anything to the records

You have to use update instead of select if you want to make changes to your db. Always back up first.

yellow1912:

Select is ....select, that's it, it doesnt do anything to the records

You have to use update instead of select if you want to make changes to your db. Always back up first.

Yellow1912,
Thank you,

UPDATE products_description SET products_description = REPLACE('hematite', 'hematite', 'magnatite');

1. Yes i am on a demo site.
2. thanks for the reminder of database backup
3. when I put the code in above it eliminate all my wording and places magnatie
4. Instead of:
5. Magnatite is a gods natural stone. etc. etc

well, because you are doing it incorrectly. Always, always read the document carefully, it is straight forward.

An example they use

mysql> **SELECT REPLACE('www.mysql.com', 'w', 'Ww');
** -> 'WwWwWw.mysql.com'
So in your case you may want to try

UPDATE products_description SET products_description = REPLACE(products_description, 'magnatite', 'hematite');

Something like that. Assuming you want to change from magnatite to hematite

Yellow1912,

Thanks for your time..
Was up working late on that little task.
Was close, but yet far from the theory.

The correct path with your help was:

UPDATE products_description SET products_description = REPLACE(products_description, 'hematite', 'magnatite');

You will also need to decide if you are going to use magnEtite or magnAtite.

Either could be correct. Magnetite is the actual mineral while magnatite is generally manufactured. Either way, make sure you have both in the keywords.

Dbltoe2,
Thanks for the English lesson and the heads up on the keywords.

Enjoy Bunco tonight...

I am attempting to do the same thing. However, I am trying to change item descriptions from 2x3' to 2x3 Foot. I get this error. HELP PLEASE! I have over 3000 products on the site and I need this to be updated ASAP. Thanks everyone.

1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2x3 Foot')' at line 1
in:
[UPDATE zen_products_description SET products_description = REPLACE('2x3'', '2x3 Foot');]
If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields.

The problem is because you are replacing something containing quotes and you are using the same quotes in your replace ...

Try using:

UPDATE zen_products_description SET products_description = REPLACE("2x3'", "2x3 Foot");

Note: you could run this command as:

UPDATE products_description SET products_description = REPLACE("2x3'", "2x3 Foot");

from the Tools ... Install SQL Patches ... and it will adjust for the database table prefix ...

Awesome Thanks. I will try that today and see how it goes for me. Thanks for getting back so quickly.

-Harrison

So I tried both of them and I got the following error. Failed: 1
Error ERROR: Cannot execute because table zen_products_description does not exist. CHECK PREFIXES! with this note: Note: 1 statements ignored. See "upgrade_exceptions" table for additional details.

Silly silly me ... slight error on the replace statement:

UPDATE products_description SET products_description = REPLACE([B]products_description, [/B]"2x3'", "2x3 Foot"); 

The REPLACE needs the field in it as well ... :smile: