Connecting string values in SQL

When I run a query like this:

UPDATE MyTable SET MyFld ='Some Value' + MyFld WHERE ID = 100

It works on the SQL Server 2005, but not on 2000, where I get the following error:

Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals boolean AND, type equals text.

What is the correct method of connecting the string values together the SQL command ?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

If you add () it should work, like the following:

UPDATE MyTable SET (MyFld ='Some Value' + MyFld) WHERE ID = 100
Scott PletcherSenior DBACommented:
You cannot directly UPDATE text columns in SQL 2000, you must use UPDATETEXT -- which is not that easy and requires using a cursor and looping thru the rows.
vpekulasAuthor Commented:
That doesn't seem to work either, when I do that I get:

Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

vpekulasAuthor Commented:
:( OK, I guess that explains it .... thanks.
Aneesh RetnakaranDatabase AdministratorCommented:
You need to use Updatetext / writetext for updating a  column

The following example gives you an example

Update  textCol1 by appending TextColumn2 and Text Col 3

CREATE TABLE TextExample (i int identity(1,1), text1 text, text2 text, text3 text)

DECLARE @txtPtr1 Varbinary(16)
DECLARE @txtPtr2 Varbinary(16)
DECLARE @txtPtr3 Varbinary(16)

SELECT @txtPtr1 = TEXTPTR(text1)
FROM TextExample

SELECT @txtPtr2 = TEXTPTR(text2)
FROM TextExample

UPDATE TextExample
SET Text3 = Text1
WHERE i = 1

SELECT @txtPtr3 = TEXTPTR(text3)
FROM TextExample
WHERE i =1

FROM TextExample
WHERE i =1

UPDATETEXT TextExample.Text3 @txtPtr3 NULL 0 ' '

FROM TextExample
WHERE i =1

UPDATETEXT TextExample.Text3 @txtPtr3 NULL 0 TextExample.Text2 @txtPtr2

FROM TextExample
WHERE i =1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

First create a function on your database.The function is as follows:

create function ConcatStrings() returns varchar(max length of your data after concatenation) as
return (select col1+col2+...(columns you want to concatenate) from your table name where condition)

the usage of function in a query

update your table set col=dbo.concatstrings()

col will be updated according to the cols specified in your function

this function is too small but you can change its contents easily
if you can't change the functino according to your data then tell me i will help you to update it according to your function
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.