CODEWITHSUNDEEP
sqlsql-server
Asaf Savich
Asaf Savich

Reputation: 653

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.

Upvotes: 3

Views: 190

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82474

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)

Upvotes: 5

hacene abdessamed
hacene abdessamed

Reputation: 559

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

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

Upvotes: 0

Related Questions