can insert into but can't update a text column

I can insert into but can't update a text column - see following example :

1) create a table with a text column

CREATE TABLE [dbo].[TestLongText] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[FileContent] [text] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

2) insert text from another table into this table
insert into  TestLongText(FileCon
tent) select FileContent from
 OtherTableWithTextCo
l where Id = 2
-- works fine

3) try to update this text column with identical text from the other table
update TestLongText set FileContent = (select FileContent from
 OtherTableWithTextCo
l where Id = 2) where id = 1
-- returns this error: The text, ntext, and image data types are invalid in
this subquery or aggregate expression.

Try something like:
code:

update TestLongText
set FileContent = o.FileContent
from TestLongText  t,OtherTableWithText
Col o
where o.Id = 2 and t.Id =1


Cristian Lefter, SQL Server MVP

"David Laub" <dlaub@wheels.com> wrote in message
news:Oa%23NfYUNFHA.3560@TK2MSFTNGP14.phx.gbl...
>I can insert into but can't update a text column - see following example :
>
> 1) create a table with a text column
>
> CREATE TABLE [dbo].[TestLongText] (
> [Id] [int] IDENTITY (1, 1) NOT NULL ,
> [FileContent] [text] NOT NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
> 2) insert text from another table into this table
> insert into  TestLongText(FileCon
tent) select FileContent from
>  OtherTableWithTextCo
l where Id = 2
> -- works fine
>
> 3) try to update this text column with identical text from the other table
> update TestLongText set FileContent = (select FileContent from
>  OtherTableWithTextCo
l where Id = 2) where id = 1
> -- returns this error: The text, ntext, and image data types are invalid
> in
> this subquery or aggregate expression.
>
>

Post Follow-up to this message