|
Home > Archive > MS SQL Server > March 2005 > can insert into but can't update a text column
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
can insert into but can't update a text column
|
|
| David Laub 2005-03-30, 7:03 pm |
| 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.
| |
| Cristian Lefter 2005-03-30, 7:03 pm |
| 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.
>
>
| |
| David Laub 2005-03-30, 7:03 pm |
| Thanks!! That solved it - i forgot about doing an update based on a
join instead of a subquery!
*** Sent via Developersdex http://www.developersdex.com ***
|
|
|
|
|