How to update multiple columns for a single table using single query

Question

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 18-Dec-12 22:39pm

Priyaaammu

Updated 18-Dec-12 22:55pm

sisir patro

v2

Add a Solution

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

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

sisir patro · Answer 1 · 2012-12-18T22:58:00

Solution 1

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

Posted 18-Dec-12 22:58pm

sisir patro

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.

Kuthuparakkal · Answer 2 · 2012-12-18T23:03:00

Solution 2

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

Posted 18-Dec-12 23:03pm

Kuthuparakkal

Comments

Priyaaammu 19-Dec-12 5:20am

Thanks for your reply...i tried your query,but getting same result as "(0 row(s) affected)".