Click here to Skip to main content
15,030,062 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi..
Following is my table structure,
SQL
create table SampleTable1
(
Test1	int,
Test2	int,
Test3	int
)


I inserted values for three columns as (1,1,1),(2,2,2),(3,3,3)

Now i want to update both 3 columns values with the symbol of '-' and i tried with the following query as

SQL
update SampleTable1 set Test1='-',Test2='-',Test3='-' where Test1 is null and Test2 is null and Test3 is null

While executing,am getting result as "(0 row(s) affected)".
What is the wrong in my query...guide me
Posted
Updated 18-Dec-12 22:55pm
v2
Comments
kankeyan 19-Dec-12 4:50am
   
Whether the table contains NULL values.
Priyaaammu 19-Dec-12 5:24am
   
No...it has integer values and now am changed to datatype as nvarchar,but same error

Hi,

Your table contains only integer fields and you are entering the character value in the Update statement. This is absolutely wrong.

Change your table column datatypes to varchar or nvarchar then only you can enter the "-" value in your columns.

Thanks
   
Comments
Priyaaammu 19-Dec-12 5:13am
   
Thanks for your comment. I changed datatype to nvarchar, and used query as
update SampleTable2 set Test1=ISNULL(test1,'-'),Test2=ISNULL(test2,'-'),Test3=ISNULL(test3,'-')


All the rows get affected,but no changes in table
[no name] 19-Dec-12 5:25am
   
Yes because your table columns may not have any null values. Can you check in your table for the null values? And let me know the data in your table.
Try:
SQL
UPDATE P1
SET 
  P1.Test1= CASE WHEN P1.Test1 IS NULL THEN '-' ELSE P1.Test1 END 
, P1.Test2= CASE WHEN P1.Test2 IS NULL THEN '-' ELSE P1.Test2 END 
, P1.Test3= CASE WHEN P1.Test3 IS NULL THEN '-' ELSE P1.Test3 END 
 
FROM SampleTable1 AS P1
WHERE P1.Test1 IS NULL OR P1.Test2 IS NULL OR P1.Test3 IS NULL
   
Comments
Priyaaammu 19-Dec-12 5:20am
   
Thanks for your reply...i tried your query,but getting same result as "(0 row(s) affected)".

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900