Post reply

Update ntext subquery

  • December 14, 2009 at 2:45 pm

    #94224

    Assuming TextColumn is an ntext, how do you do this?:

    UPDATE SomeTable SET Column = (SELECT TextColumn FROM OtherTable WHERE OtherID = @ID) WHERE SomeID = '1'

    I get this error:

    The text, ntext, and image data types are invalid in this subquery or aggregate expression.

    I can't cast it to varchar since the character length of some of these columns exceed 8000. I can't declare ntext variables either.

  • December 14, 2009 at 2:54 pm

    #1092240

    Why the subquery at all?

    UPDATE SomeTable SET Column = TextColumn

    FROM SomeTable INNER JOIN OtherTable ON <join condition here>

    WHERE OtherID = @ID

    AND WHERE SomeID = 1 -- not in quotes unless the SomeID column is char/varchar

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 15, 2009 at 7:56 am

    #1092533

    Thanks, so simple! *wack myself on the head*

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply