Selecting a TEXT field via SQL

[darrel]

I'm trying to copy a TEXT field from one table to another:

UPDATE dbo.WeSiteMenus
SET pageContent =
(SELECT pageContent
FROM WeSiteMenusArchive
WHERE archiveID = 1208)
WHERE (pageID = 1255)

However, I keep getting a

'The text, ntext, and image data types are invalid in this subquery or
aggreagate expression'

My understanding is that this is because I'm trying to SELECT a TEXT as a
nested query. I'm not sure what the workaround for this would be, though. Do
I need to write a function that get's the TEXT field by a lone SELECT
statement, then send it back with a separate UPDATE statement?

-Darrel

[Ray Booysen]

darrel wrote:

I'm trying to copy a TEXT field from one table to another:

UPDATE dbo.WeSiteMenus
SET pageContent =
(SELECT pageContent
FROM WeSiteMenusArchive
WHERE archiveID = 1208)
WHERE (pageID = 1255)

However, I keep getting a

'The text, ntext, and image data types are invalid in this subquery or
aggreagate expression'

My understanding is that this is because I'm trying to SELECT a TEXT as a
nested query. I'm not sure what the workaround for this would be, though. Do
I need to write a function that get's the TEXT field by a lone SELECT
statement, then send it back with a separate UPDATE statement?

-Darrel

Try asking in an SQL newsgroup. You'll get better answers there. ;)

[bruce barker \(sqlwork.com\)]

this is supported in sql2005, not sure what version you are running. try
using join instead:

UPDATE dbo.WeSiteMenus
SET pageContent = a.pageContent
from dbo.WeSiteMenus m
join WeSiteMenusArchive a on archiveID = 1208
WHERE m.pageID = 1255

-- bruce (sqlwork.com)
"darrel" <no*****@nowhere.com> wrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...

I'm trying to copy a TEXT field from one table to another:

UPDATE dbo.WeSiteMenus
SET pageContent =
(SELECT pageContent
FROM WeSiteMenusArchive
WHERE archiveID = 1208)
WHERE (pageID = 1255)

However, I keep getting a

'The text, ntext, and image data types are invalid in this subquery or
aggreagate expression'

My understanding is that this is because I'm trying to SELECT a TEXT as a
nested query. I'm not sure what the workaround for this would be, though.
Do I need to write a function that get's the TEXT field by a lone SELECT
statement, then send it back with a separate UPDATE statement?

-Darrel