Copying NTEXT data from one record to another

tcarnahans · #1 (**permalink**) 09-01-10, 12:22

I tried seaching the archive without much success, so here is my situation.

I have a SS2K database.

I have a table (MyTable) with one NTEXT field (MyNtext) and a prime key (PK). and I need to copy data from the NTEXT field in one record to the same field in another record and overwrite the data.

I tried using:

Code:

UPDATE MyTable
SET MyNtext = (SELECT MyNtext FROM MyTable WHERE PK = 44)
WHERE PK = 66

I get the error code:

Quote:

.Net SqlClient Dataq Provider: Msg 279, Level 16, State 3, Line 40
The ntext data type is invalid in the subqquery or aggregate expression.

I tried playing around with READTEXT and WRITETEXT, but I don't know how to get the output from the READTEXT to feed the input of the WRITETEXT.

Does anyone have an example of how to do this? Any help would be appreciated!

Wim · #2 (**permalink**) 09-01-10, 12:42

This worked in MSSQL 2008:

Code:

DROP TABLE MyTable
CREATE TABLE MyTable (
	pk int NOT NULL,
	mytext NTEXT
)
INSERT INTO MyTable (pk, mytext) VALUES (44, 'kklkl')
INSERT INTO MyTable (pk, mytext) VALUES (66, 'blabla')

UPDATE MyTable
SET Mytext = (SELECT Mytext FROM MyTable WHERE PK = 44)
WHERE PK = 66

select * from MyTable

Wim · #3 (**permalink**) 09-01-10, 12:56

This works too:

Code:

UPDATE MyTable
SET MyTable.Mytext = Src.mytext
FROM MyTable, MyTable as Src
WHERE MyTable.PK = 66 AND
	Src.PK = 44

tcarnahans · #4 (**permalink**) 09-01-10, 13:19

It worked!

I am not sure why the other did not work.

Many thanks! You are an angel

Internet Services

Web Development

Digital Marketing

Consumer Tech