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';
- admin panel/ tools/ install sql patches
- I insert the following and get 1 processed but no change
- What is wrong... :lamo:
Re: SQL command to find and replace a word in the products description
Re: SQL command to find and replace a word in the products description
Quote:
Originally Posted by
yellow1912
Yellow1912,
SELECT REPLACE('hematite', 'hematite', 'magnatite');
- I click send
- 1 statement processed
- But no change on the page.
- What is wrong???
Re: SQL command to find and replace a word in the products description
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.
Re: SQL command to find and replace a word in the products description
Quote:
Originally Posted by
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');
- Yes i am on a demo site.
- thanks for the reminder of database backup
- when I put the code in above it eliminate all my wording and places magnatie
- Instead of:
- Magnatite is a gods natural stone. etc. etc
Re: SQL command to find and replace a word in the products description
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
Re: SQL command to find and replace a word in the products description
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:
Quote:
UPDATE products_description SET products_description = REPLACE(products_description, 'hematite', 'magnatite');
Re: SQL command to find and replace a word in the products description
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.
Re: SQL command to find and replace a word in the products description
Dbltoe2,
Thanks for the English lesson and the heads up on the keywords.
Enjoy Bunco tonight...
Re: SQL command to find and replace a word in the products description
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.