none
Updating a text field with a sub-query

    Question

  • I have always avoided text fields because of lack of motivation to learn the ways to work with it...but I can't avoid it with the current project I'm working on.

    I need to copy information from an old database into one with similar structure...a lot of the information is there, but some things still need to be synch'ed up.  The problem I'm having is copying the description field from the old database to the new one because they are text fields. 

    If I were doing this with varchars I would use the query below...but since they aren't I get the "can't use text fields in sub-queries" error.  Can anybody explain to me how I would accomplish this task?

    UPDATE courses
    SET courses.Description = 
       (SELECT Descrip 
        FROM Import_Courses
              INNER JOIN courses ON courses.course_id = Import_Courses.CourseID
        WHERE Import_Courses.Descrip IS NOT NULL)

    Thanks a lot in advance,
    Greg

    Tuesday, June 14, 2005 4:34 PM

Answers

  • You don't need a sub-query in this instance to update the text column. You can rewrite your query using the TSQL update syntax like below which allows you to use the text column in the SET clause.

    UPDATE courses
    SET courses.Description = i.Descrip
    FROM Import_Courses as i
    WHERE i.course_id = courses.course_id
    AND Import_Courses.Descrip IS NOT NULL

    For more details, on the TSQL extension to UPDATE refer to the UPDATE topic in Books Online.
    Tuesday, June 14, 2005 5:42 PM

All replies

  • You don't need a sub-query in this instance to update the text column. You can rewrite your query using the TSQL update syntax like below which allows you to use the text column in the SET clause.

    UPDATE courses
    SET courses.Description = i.Descrip
    FROM Import_Courses as i
    WHERE i.course_id = courses.course_id
    AND Import_Courses.Descrip IS NOT NULL

    For more details, on the TSQL extension to UPDATE refer to the UPDATE topic in Books Online.
    Tuesday, June 14, 2005 5:42 PM
  • Thanks a lot...not sure why I didn't think about doing it that way.
    Tuesday, June 14, 2005 6:06 PM
  • Thanks. Even though I did not ask this question, I was trying to do something similar. Due to this posting, my problem is solved. Thanks a lot.
    Thursday, January 04, 2007 12:46 AM