SQL UPDATE REPLACE

vacpartswarehouse
vacpartswarehouse used Ask the Experts™
on
Hello,

I am trying to run this example through my Volusion CMS bulk update feature. This throws an error for some reason. Any ideas? The CategoryDescription is set to MEMO if that helps any.

UPDATE Categories
SET CategoryDescription = REPLACE(CategoryDescription,'A', 'B')
WHERE CategoryID = 1151


I can run...

UPDATE Categories
SET CategoryDescription = 'ABC'
WHERE CategoryID = 1151

... without any issues.

Thanks for the help in advance!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
Can you post the error and is it CategoryDescription "character or binary data type"?

Author

Commented:
Says CategoryDescription is Any characters (no limit).

 Error is: An error has occurred. Please try your request again, or contact customer service for assistance. Thank you. Please provide Reference #25145812 if contacting customer service.
lcohanDatabase Analyst

Commented:
That is NOT a SQL data type. Could you please check what data type CategoryDescription sql table.column is?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

GlobaLevelProgrammer

Commented:
UPDATE c
SET c.CategoryDescription = REPLACE(c.CategoryDescription,'A', 'B')
from   Categories c
WHERE c.CategoryID = 1151

Author

Commented:
Different error after that:

An error has occurred. Please try your request again, or contact customer service for assistance. Thank you. Please provide Reference #25146102 if contacting customer service.

Author

Commented:
Says data type is MEMO
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Do you know what database platform the Volusion CMS is running on?  From some searches I suspect MySQL, but not sure.  The potential issue is that the data type that is on the database end of things to support the 'MEMO' field exposed to the front end doesn't support REPLACE() function and possibly you have to convert first OR use some other function.  Does Volusion have a support site that allows you to lookup error messages or manual on proper SQL to use.  Knowing platform will help us help you a bit better, though.

Author

Commented:
http://support.volusion.com/

I'm looking for the platform, but this is the support site link

Author

Commented:
I just asked support about the error messages. Waiting for response

Author

Commented:
Support says the database platform is: IIS 6
Commented:
try this...

UPDATE Categories
SET CategoryDescription = REPLACE(cast(CategoryDescription as varchar(70)),'A', 'B')
WHERE CategoryID = 1151

Author

Commented:
Worked perfectly! Yes, also works for the actual intended application as well. You are my hero!

Author

Commented:
Thanks!

Commented:
you are very welcome. Glad it worked well!
hi

work functionlity about replace function...
Replace(COLUMN NAME, ROW VALUE, REPALCE STRING VALUE)
example
id cat_name
1   Retail
Replace(CAT_NAME, RETAIL, 'CASHPAY')

OUTPUT
id cat_name
1  CASHPAY
i hope now u have idea about replace fun..
and then  update with replace query
update tablename set column name= Replace(COLUMN NAME, ROW VALUE, REPALCE STRING VALUE))

Example
update Categories set CategoryDescription = Replace(CategoryDescription , 'A', 'ABC')
 CategoryID = 1151

i hope this explanation is useful for you

by
kumaresan
update Categories set CategoryDescription = Replace(CategoryDescription , 'A', 'ABC')
 CategoryID = 1151

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial