asked on

SQL UPDATE REPLACE

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!

lcohan

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

vacpartswarehouse

ASKER

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.

lcohan

That is NOT a SQL data type. Could you please check what data type CategoryDescription sql table.column is?

GlobaLevel

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

vacpartswarehouse

ASKER

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.

vacpartswarehouse

ASKER

Says data type is MEMO

Kevin Cross

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.

vacpartswarehouse

ASKER

http://support.volusion.com/

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

vacpartswarehouse

ASKER

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

vacpartswarehouse

ASKER

Support says the database platform is: IIS 6

ASKER CERTIFIED SOLUTION

twol

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vacpartswarehouse

ASKER

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

vacpartswarehouse

ASKER

Thanks!

twol

you are very welcome. Glad it worked well!

kumaresan2011

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