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

Question

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.

Community · Accepted Answer · 2017-05-23 12:31:48Z

5

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)

edited May 23, 2017 at 12:31

CommunityBot

11 silver badge

answered May 10, 2016 at 13:20

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sean Lange Over a year ago

Of if the default is the same for each nested case expression you could simply wrap the outermost case expression with ISNULL.

Zohar Peled Over a year ago

@SeanLange: You are correct, of course, why didn't I think about it?

Asaf Savich Over a year ago

@SeanLange Can you give me an example how it would look like?

hacene abdessamed · Accepted Answer · 2016-05-10 13:23:26Z

0

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

answered May 10, 2016 at 13:23

hacene abdessamed

5696 silver badges22 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related