有条件地更新多列

Question

日安！我目前在更新我的数据库方面遇到了困难。我正在使用c#.net编写代码并使用SQL server。

我的问题是，我试图有条件地更新多个列。这是我的密码：

"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 + "'"

如果Milestone1等于今天的日期(araw.text)，那么里程碑状态将被更改。否则，milestone_status将不会被更改。milestone2到milestone5的情况也是如此。

我的代码是SQL语句不工作。任何帮助都将不胜感激。

Answer 1

尝试；

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

这将更新

Milestone_status与m1，if Milestone1 = araw.Text

Milestone_status与m2，if Milestone2 = araw.Text

Milestone_status和m3，如果Milestone3 = araw.Text..。

如果不满足任何条件，那么Milestone_status将不会被修改。

Answer 2

将其从C#中删除，并将其单独考虑为SQL语句，只需简单地查看milestone1，您的SQL就必须如下所示：

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

也就是说，如果milestone1是今天的日期，那么将milestone_status设置为@m1，否则将其设置为自身(即不要更改它)。

SQL有几个关于用例语法的好例子，为了清晰起见，可能也值得回顾一下：https://msdn.microsoft.com/en-us/library/ms181765.aspx。

希望这有帮助

Answer 3

您的CASE语句正在编写语法：

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'

正确的语法必须类似于

CASE expression

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

   ELSE result

END

在您的示例中，可以使用嵌套案例检查每个里程碑(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 + "'"