如何根据多个参数用不同的值更新sql中的多个行

I'm looking to update multiple rows based on a few parametrs with different values.
What do i mean? I'm glad you asked.
My Table looks like this right now:

我希望基于一些具有不同值的参数更新多个行。我的意思是什么?我很高兴你问。我的桌子现在是这样的:

-------------
MyTable
client_id    api_id    index  msg_id  settings
123          4         10     6       0
123          4         10     7       1
123          4         10     8       1
123          4         11     7       1
123          5         10     8       1
456          4         10     9       1

The table I want after the change is: (change only the settings column)

更改后我想要的表是:(只更改settings列)

-------------------
MyTable
client_id    api_id    index  msg_id  settings
123          4         10     6       1 (changed)
123          4         10     7       2 (changed)
123          4         10     8       1 (changed to the same thing)
123          4         11     7       1
123          5         10     8       1
456          4         10     9       1

What i'm trying to do right now is:

我现在想做的是:

UPDATE myTable SET settings = 
CASE client_id
    WHEN '123' THEN
        CASE api_id
            WHEN '4' THEN
                CASE index
                    WHEN '10' THEN
                        CASE msg_id
                            WHEN '6' THEN 1
                            WHEN '7' THEN 2
                            WHEN '8' THEN 1
                            WHEN '9' THEN 0
                            ...
                        END
                END
        END
END

The Exception that i keep getting is

我一直得到的例外是。

Cannot insert the value NULL into column 'settings', table 'myTable'; column does not allow nulls. UPDATE fails.

不能将值NULL插入列'settings'中，表'myTable';列不允许为空。更新失败。

So my questions are:

所以我的问题是:

A. Is what i'm asking possible?

我要求的是可能的吗?

B. If it is possible. What am i doing wrong?

如果可能的话。我做错了什么?

Thanks in advance.

提前谢谢。

2 个解决方案

#1

You need to add ELSE for each CASE expression, and set the default value (I'm guessing it's 0):

您需要为每个CASE表达式添加ELSE，并设置默认值(我猜是0):

UPDATE myTable SET settings = 
CASE client_id
    WHEN '123' THEN
        CASE api_id
            WHEN '4' THEN
                CASE index
                    WHEN '10' THEN
                        CASE msg_id
                            WHEN '6' THEN 1
                            WHEN '7' THEN 2
                            WHEN '8' THEN 1
                            WHEN '9' THEN 0
                            ...
                        ELSE 0
                        END
                    ELSE 0
                END
            ELSE 0
        END
    ELSE 0
END

Or much simpler, as Sean Lange suggested in his comment, just wrap the entire case with ISNULL:

或者更简单，就像Sean Lange在他的评论中建议的那样，用ISNULL来结束整个案例:

UPDATE myTable SET settings = 
ISNULL(CASE client_id
    WHEN '123' THEN
        CASE api_id
            WHEN '4' THEN
                CASE index
                    WHEN '10' THEN
                        CASE msg_id
                            WHEN '6' THEN 1
                            WHEN '7' THEN 2
                            WHEN '8' THEN 1
                            WHEN '9' THEN 0
                            ...
                        END
                END
        END
END, 0)

#2

in your case when the client_id is different to '123' in will set NULL ,I suppose your column settings should not be null

在您的示例中，当client_id与will中的'123'不同时，我认为您的列设置不应该是NULL

if you delete your first CASE client_id WHEN '123' it will works fine

如果您删除第一个CASE client_id，当'123'时，它将正常工作

#1