Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2010
    Posts
    6

    Question Unanswered: Copying NTEXT data from one record to another

    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:

    .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!

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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
    Last edited by Wim; 09-01-10 at 12:49. Reason: Reread question.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This works too:
    Code:
    UPDATE MyTable
    SET MyTable.Mytext = Src.mytext
    FROM MyTable, MyTable as Src
    WHERE MyTable.PK = 66 AND
    	Src.PK = 44
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Sep 2010
    Posts
    6

    Thumbs up Thanks

    It worked!

    I am not sure why the other did not work.

    Many thanks! You are an angel

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •