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(FileContent) select FileContent from
OtherTableWithTextCol 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
OtherTableWithTextCol 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,OtherTableWithTextCol o
where o.Id = 2 and t.Id =1
[/code]
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(FileContent) select FileContent from
> OtherTableWithTextCol 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
> OtherTableWithTextCol where Id = 2) where id = 1
> -- returns this error: The text, ntext, and image data types are invalid
> in
> this subquery or aggregate expression.
>