Text and varchar are incompatible

Transact-SQL
1

I am getting this message:
The data types text and varchar are incompatible in the equal to operator.

Here is my code:

UPDATE SalesData
SET
Region = 'West'
WHERE
Employee = 'Paulo Tocha'

The Region and Employee columns are of type "text" instead of "varchar" so I don't see why I'm getting this message.

2

You can't directly work with TEXT columns like that, you have to use READTEXT, WRITETEXT or UPDATETEXT. It's a horribly painful system which is one of the reasons why TEXT has long been deprecated in favour of VarChar(Max)

3 

create table #SalesData(Region text, Employee TEXT)

insert into #SalesData
select 'East', 'Puff Daddy' union
select 'West', 'Big E' union
select 'North', 'Thor' union
select 'South', 'Paulo Tocha'

UPDATE tgt
   SET tgt.Region = N'West'
   --select *
  from #SalesData tgt
 WHERE CONVERT(NVARCHAR(MAX), tgt.Employee) =  N'Paulo Tocha'

select * From #SalesData

drop table #SalesData
4

Wow. When you say tgt should I literally type in tgt as if it were a keyword, or am I supposed to use the table name where you have tgt?

Also, why does 'West' and 'Paulo Tocha' have the letter N prefixed before them? What does that mean? I presume it has something to do with NVARCHAR as the data type?

5

This is nice to know. Thank you.

6

tgt is just an alias for the table Sales data, you dont have to do it that way, just my habit.
N prefix makes the value that comes after it nvarchar. from MS

https://docs.microsoft.com/en-us/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql?view=sql-server-2017

Prefix Unicode character string constants with the letter N. Without the N prefix, the string is converted to the default code page of the database. This default code page may not recognize certain characters.

lets say you want to do Dônà ètienne as the name, if your database does not have the proper code page, it might not update properly, you could get squares for those letters with diacritic.

7

UPDATE SalesData
SET
Region = CAST('West' AS text)
WHERE
Employee = CAST('Paulo Tocha' AS text)

Btw, you should change the data types for these columns (at least). There's no reason for a Region or Employee name to be "text" data type.