single UPDATE query to modify multiple columns on multiple conditions SQL

Question

i'm having a sql table consisting of three columns ID,KEY and VALUE now i want to update this table's specific rows based the KEY column associated with that row.

i tried following UPDATE query to update multiple rows in a single update query.

UPDATE TABLE_NAME
SET VALUE=(CASE WHEN [KEY]='KEY1' THEN 'VALUE 1 MODIFIED'
                WHEN [KEY]='KEY3' THEN 'VALUE 3 MODIFIED'
          END)

but this query updated the unmatched rows with NULL value as shown in below fig.

enter image description here

is their any other way to write single UPDATE query to modify different columns based on different conditions?

You need to include an ELSE VALUE END in the CASE statement. If no when matches, the CASE returns null. — user330315
– user330315, Commented Nov 2, 2013 at 7:53

M.Ali · Accepted Answer · 2013-11-02 11:16:13Z

3

UPDATE TABLE_NAME
SET VALUE=(CASE WHEN [KEY]='KEY1' THEN 'VALUE 1 MODIFIED'
                WHEN [KEY]='KEY3' THEN 'VALUE 3 MODIFIED'
            ELSE ''
          END)

This will set the unmatched rows to an empty string '' but you can set a default value if you want. But if you want that column to retain its value then you could so something like this.

UPDATE TABLE_NAME
SET VALUE=(CASE WHEN [KEY]='KEY1' THEN 'VALUE 1 MODIFIED'
                WHEN [KEY]='KEY3' THEN 'VALUE 3 MODIFIED'
            ELSE VALUE
          END)

answered Nov 2, 2013 at 11:16

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

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

1 Comment

Rahul Patel Over a year ago

I'll prefer this: UPDATE TABLE_NAME SET VALUE=(CASE WHEN [KEY]='KEY1' THEN 'VALUE 1 MODIFIED' WHEN [KEY]='KEY3' THEN 'VALUE 3 MODIFIED' END) where KEY in ('KEY1','KEY2') as this query will only update required two records. So, if the KEY column is indexed, it will be faster. Retaining old value in Else part is avoided, causing better performance.

Collectives™ on Stack Overflow

single UPDATE query to modify multiple columns on multiple conditions SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related