Append to text type field in an update statement.  
Author Message
StevenKeeling





PostPosted: Tue Feb 28 16:21:27 CST 2006 Top

SQL Server >> Append to text type field in an update statement.

I have a table with one text type column. I am trying to append this
coulmn with whatever it has with another string. This field may have
more than 8000 characters already
here is What I am trying to do.

Update X
Set textTypeColumn = textTypeColumn + ' XXXXXXXXXXXXXXXXXXXXXXX'
WHERE
id = id
-- some criteria with different joins to different tables.

' XXXXXXXXXXXXXXXXXXXXXXX' will be a fixed string

TextTypeColumn may already have more than 8000 charaters in it and now
I want to append the XXXXXXX string to that and store the new value in
the texttypecolumn. This update statement is inside a stored proc. When
I try to compile it, I am getting below error.

Server: Msg 403, Level 16, State 1, Procedure
stp_scr_postmass_denyopclaims, Line 426
Invalid operator for data type. Operator equals add, type equals text.

I want to avoid cursor and looping for each id and then use Updatetext
to update it.
I would appreciate if you could let me know if it can be done in a
query for all the ids in one go.
Thanks & regards,
Mandar

SQL Server1  
 
 
ravishankart





PostPosted: Tue Feb 28 16:21:27 CST 2006 Top

SQL Server >> Append to text type field in an update statement. Text datatype won't support UPDATEs. Check BOL
--
Thanks & Rate the Postings.
-Ravi-




> I have a table with one text type column. I am trying to append this
> coulmn with whatever it has with another string. This field may have
> more than 8000 characters already
> here is What I am trying to do.
>
> Update X
> Set textTypeColumn = textTypeColumn + ' XXXXXXXXXXXXXXXXXXXXXXX'
> WHERE
> id = id
> -- some criteria with different joins to different tables.
>
> ' XXXXXXXXXXXXXXXXXXXXXXX' will be a fixed string
>
> TextTypeColumn may already have more than 8000 charaters in it and now
> I want to append the XXXXXXX string to that and store the new value in
> the texttypecolumn. This update statement is inside a stored proc. When
> I try to compile it, I am getting below error.
>
> Server: Msg 403, Level 16, State 1, Procedure
> stp_scr_postmass_denyopclaims, Line 426
> Invalid operator for data type. Operator equals add, type equals text.
>
> I want to avoid cursor and looping for each id and then use Updatetext
> to update it.
> I would appreciate if you could let me know if it can be done in a
> query for all the ids in one go.
> Thanks & regards,
> Mandar
>
>