Update Multiple columns?

Question

I want to update multple columns if it reaches my condition for e.g

i want to do something like

set column1 ,column 2 ,column 3
if column1=0 if column2=0 if column3=0;
to column1=somevalue,column2=somevalue,column3=somevalue;

Below is my query which works for 1 column only iwant to do this for more then 1 columns but in the same query.

SqlCommand cmd = new SqlCommand("UPDATE DwH_Staging_table SET pestpopulation1=@avg_pest1 

WHERE DistrictName=@DistrictName and TownName=@TownName And VarietyOfCrop=@V_Crop  And 
pestpopulation1=@pest", con1);

cmd.Parameters.AddWithValue("@avg_pest1",row[3]);

cmd.Parameters.AddWithValue("@DistrictName", row[0].ToString());

cmd.Parameters.AddWithValue("@TownName",row[1].ToString());

cmd.Parameters.AddWithValue("@V_Crop",row[2].ToString());

cmd.Parameters.AddWithValue("@pest",0);

cmd.ExecuteNonQuery();

Szymon · Accepted Answer · 2013-11-24 10:47:22Z

1

If you want to set your columns if ALL existing values are 0, you can do it in one query

UPDATE mytable
SET column1 = @value1, column2 = @value2, column3 = @value3
WHERE column1 = 0 AND column2 = 0 AND column3 = 0

But I'm guessing you want to set them separately if each column separately equals 0. In this case, you need to execute 3 separate queries.

UPDATE mytable
SET column1 = @value1
WHERE column1 = 0

UPDATE mytable
SET column2 = @value2
WHERE column2 = 0

UPDATE mytable
SET column3 = @value3
WHERE column3 = 0

answered Nov 24, 2013 at 10:47

Szymon

43k16 gold badges99 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user1056466 Over a year ago

e.g column1 value is 0 and column2 is 9 and column3 is 0 i want my query to check this row only one time.not multiple times because it will take too much time for alot of data

Szymon Over a year ago

Then you can modify the first query and replace zeroes with your values.

user1056466 Over a year ago

But if i do that it replaces all the values i want only some of the values being replaced but not the others e.g if column=0, column=9, column=0; i want to replace the columns with 0 to some avg!!

user1056466 Over a year ago

More over the number of comparisons in my case become too high as my data is quite huge and i want to reduce these comparisons to save time . so i can not use update like in the second example you gave !

Szymon Over a year ago

Sorry, then you have to do separate queries. You use one query if you want to update values separately. It just doesn't work this way.

Collectives™ on Stack Overflow

Update Multiple columns?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related