NText issue 1

[Ascalonian]

I have two tables (table_one, table_two). These are mirror tables, so they contain the same columns.

I am trying to work with two columns: key_code and proposal_text. Both tables have the same values in key_code but table_two has all NULLs in proposal_text.

So I am tasked with moving the proposal_text from table_one into table_two by means of matching the key_codes. (Essentially someone erased the data out of table_two and need to repopulate it from table_one).

So I wrote the following update statement:

update table_two
set proposal_text = (SELECT proposal_text 
                     FROM table_one tOne
                    WHERE tOne.key_code = key_code)

Looks fine, but I get the following error:
SQL Server Database Error: The text, ntext, and image data types are invalid in this subquery or aggregate expression.

Any ideas? Thank you.

 

[gmmastros]

try this:

update table_two
set    table_two.proposal_text = table_one.proposal_text
FROM   Table_two
       inner join table_one
         On table_two.key_code = table_one.key_code

If you have questions about it, let me know.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Ascalonian]

That worked perfectly! Thank you so much!

Now... why was I getting the error? Just so I can learn from this.

 

[gmmastros]

Just so I can learn from this.

I'm glad you want to learn.

Our queries are similar in many ways, but also different. You query used a subquery to get the data from the other table. My query did a join. Joins are more efficient than sub queries. And.... they don't suffer from the error you were originally getting.

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

Basically, sub-queries (under most circumstances) should be avoided. There are times when a sub-query will outperform a join, but it's rare.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Ascalonian]

I am an Oracle guy and got thrown into the SQL Server land in the last couple of months. Still learning.

Thank you very much for your help! Have a star