update sql syntax - reset a field to itself with a concatenation - sql server 2005

Question

I'm getting and error using this syntax:

update table set field1 = (field1+' - '+field2) where field1 = 'somevalue'

It's not too happy with doing this for me. I know that the '+' for concatenation works in my select statements, so that is the right syntax. There is something else at play here... and I tried removing the parenthesis too.

Example:

if field1 = 'Cheese' and field2 = 'ConQueso'
then my update should set all records where field1 = 'Cheese' to field1 = 'Cheese - ConQueso'

---

EDIT:
Both fields are text fields

Answer 1

Hard to tell without you providing the error, but perhaps the combined data size exceeds field1.

E.g., if field1 is varchar(50) and field2 is varchar(50), the combined total could be up to 103 characters including your ' - ', which exceeds the 50 characters of field1.

Answer 2

(edit: pre-dates the update clarifying data-type is text; but works fine as varchar(max))

Works fine here (SQL2005):

create table [table] (
   field1 varchar(max) not null,
   field2 varchar(max) not null)
insert [table] values ('somevalue','abc')
insert [table] values ('other','def')
update [table] set field1 = (field1+' - '+field2) where field1 = 'somevalue'
select * from [table]

outputs:

field1               field2
-------------------- --------------------
somevalue - abc      abc
other                def