AsafSavich AsafSavich - 6 months ago 59
SQL Question

How to update multiple rows in sql with different values according to multiple parameters

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)

-------------------
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.


So my questions are:

A. Is what i'm asking possible?

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

Thanks in advance.

Answer

You need to add ELSE for each CASE expression, and set the default value (I'm guessing it's 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:

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)