CODEWITHSUNDEEP
c#sqlsql-server
John F
John F

Reputation: 142

Update multiple columns conditionally

Good Day! I'm currently having trouble in updating my database. I'm coding in c#.net and using SQL server.

My problem is that I'm trying to update multiple columns conditionally. Here's my code:

"Update Eureka Set Milestone1 = Case When Milestone1 = '" + araw.Text + "' then Milestone_status = '" + m1 + "' End, Milestone2 = When Milestone2 = '" + araw.Text + "' then Milestone_status = '" + m2 + "' End, Milestone3 = When Milestone3 = '" + araw.Text + "' Then Milestone_status = '" + m3 + "' End, Milestone4 = When Milestone4 = '" + araw.Text + "' Then Milestone_status = '" + m4 + "' End, Milestone5 = When Milestone5 = '" + araw.Text + "' Then Milestone_status = '" + m5 + "' End Where Eureka_id = '" + eid.Text + "'"

If Milestone1 is equal to today's date (araw.text) then the milestone status will be changed. If not, the milestone_status won't be changed. Then the same goes for milestone2 to milestone5.

My code is SQL statement is not working. Any help would be appreciated.

Upvotes: 0

Views: 112

Answers (3)

Praveen
Praveen

Reputation: 9345

Try;

Update Eureka 
Set Milestone_status = 
    Case 
        When Milestone1 = @araw_Text then @m1
        when Milestone2 = @araw_Text then @m2
        when Milestone3 = @araw_Text then @m3
        when Milestone4 = @araw_Text then @m4
        when Milestone5 = @araw_Text then @m5
        else Milestone_status
    end
Where Eureka_id = @eid_Text

This will update
Milestone_status with m1, if Milestone1 = araw.Text
Milestone_status with m2, if Milestone2 = araw.Text
Milestone_status with m3, if Milestone3 = araw.Text....

If no condition is satisfied then Milestone_status will not be modified

Upvotes: 1

Roman Marusyk
Roman Marusyk

Reputation: 24589

Your CASE statement is worng syntax:

Update Eureka 
Set Milestone1 = Case When Milestone1 = 'araw.Text' then Milestone_status = 'm1' End
,   Milestone2 = When Milestone2 = 'araw.Text' then Milestone_status = 'm2' End
,   Milestone3 = When Milestone3 = 'araw.Text' Then Milestone_status = 'm3' End
,   Milestone4 = When Milestone4 = 'araw.Text' Then Milestone_status = 'm4' End
,   Milestone5 = When Milestone5 = 'araw.Text' Then Milestone_status = 'm5' End 
Where Eureka_id = 'eid.Text'

The proper syntax have to be like

CASE expression

   WHEN value_1 THEN result_1
   WHEN value_2 THEN result_2
   ...
   WHEN value_n THEN result_n

   ELSE result

END

In your case you can use nested CASE to check each Milestone(Milestone1,Milestone2,..)

"Update Eureka
set Milestone_status = case when Milestone1 = '" + araw.Text + "' then '" + m1 + "' else 
                         case when Milestone2 = '" + araw.Text + "' then '" + m2 + "' else
                           case when Milestone3 = '" + araw.Text + "' then '" + m3 + "' else
                             case when Milestone4 = '" + araw.Text + "' then '" + m4 + "' else
                               case when Milestone5 = '" + araw.Text + "' then '" + m5 + "' else Milestone_status end
                              end
                            end                              
                          end
                        end
Where Eureka_id = '" + eid.Text + "'"

Upvotes: 0

PulseLab
PulseLab

Reputation: 1579

Taking it out of the C# for a moment and considering it as a SQL statement alone, and just looking at milestone1 for simplicity, your SQL would have to be something like:

UPDATE Eureka
   SET milestone_status = 
      CASE WHEN milestone1 = @todays_date then @m1
      ELSE milestone_status
      END

i.e. if milestone1 is todays date then set milestone_status to @m1, else set it to itself (i.e. don't change it).

SQL docs has several good examples on use of the CASE syntax, might be worth reviewing too for clarity: https://msdn.microsoft.com/en-us/library/ms181765.aspx

Hope that helps

Upvotes: 1

Related Questions