Prev: Trigger that acts on either the NEW pk or the existing pk
Next: Question of Views
From: Mary Phelps on 12 Aug 2010 01:06
I have a query that involves 3 tables. I am only mentioning data types
of columns that are involved in the query. Table customer with column
customerid(int), table discount with column validforcutomers(ntext),
registration with email(varchar(50)). I am ruunning a query which is
giving me the error mentioned in the subject of this message.

update discount set ValidForCustomers=(
select customerid from customer where Email in
(select Email from Registration where dog='2010-07-31'))
where DiscountID=14
From: Erland Sommarskog on 12 Aug 2010 03:35
Mary Phelps (icanhelp33(a)gmail.com) writes:
> I have a query that involves 3 tables. I am only mentioning data types
> of columns that are involved in the query. Table customer with column
> customerid(int), table discount with column validforcutomers(ntext),
> registration with email(varchar(50)). I am ruunning a query which is
> giving me the error mentioned in the subject of this message.
>
> update discount set ValidForCustomers=(
> select customerid from customer where Email in
> (select Email from Registration where dog='2010-07-31'))
> where DiscountID=14

And what's wrong with that? Consider it a good thing! It's just too bad that
you don't get this error also when you target a varchar column. Implicit
conversion of strings to numbers and vice versa is really a bad idea, and
opens for bugs that should have been caught at compile time.

Use convert() or str() convert the numeric value to string.


(And if you are on SQL 2005 or later, beware of that ntext is deprecated,
and you should use nvarchar(MAX) instead. nvarchar(MAX) is a lot easier to
work with ntext, as it works as a normal nvarchar.)

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Mary Phelps on 12 Aug 2010 04:54
Where should I use convert in this query. I tried but it fails.

From: Scott Morris on 12 Aug 2010 08:44
"Mary Phelps" <icanhelp33(a)gmail.com> wrote in message
news:2187a0e8-5850-4893-bd79-7c9bc6eba6d9(a)l25g2000prn.googlegroups.com...
> Where should I use convert in this query. I tried but it fails.

Think about your columns for a second. Which one is ntext? How is it used?
If it is used in an assignment (hint), what value are you attempting to
assign to it? What is the datatype of that value?


From: Erland Sommarskog on 12 Aug 2010 18:23
Mary Phelps (icanhelp33(a)gmail.com) writes:
> Where should I use convert in this query. I tried but it fails.

Fails with what? What did you try?

You can either wrap the entire subquery in convert, or just customerid.
Most people would do the latter.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 |  Next  |  Last
Pages: 1 2
Prev: Trigger that acts on either the NEW pk or the existing pk
Next: Question of Views