I am getting this message:
The data types text and varchar are incompatible in the equal to operator.
Here is my code:
Region = 'West'
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.
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)
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'
SET tgt.Region = N'West'
from #SalesData tgt
WHERE CONVERT(NVARCHAR(MAX), tgt.Employee) = N'Paulo Tocha'
select * From #SalesData
drop table #SalesData
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?
This is nice to know. Thank you.
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
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.
Region = CAST('West' AS text)
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.