CODEWITHSUNDEEP
sql-server-2008sql-update
Hemant Sisodia
Hemant Sisodia

Reputation: 498

How to update in SQL based on different where clause

I have a table which has a structure as:

ID  DBInstance  DBName  Tag
1  | INS1     | master | NULL
2  | INS1     | tempdb | NULL
4  | INS2     | master | NULL
5  | INS2     | tempdb | NULL

I want to update the tag in this table as based on the condition as: 1) Update tag as "a" only for DBInstance as "INS1" and DBName as "master" 2) Update tag as "b" only for DBInstance as "INS2" and DBName as "tempdb".

And I want to update both of these two only in a single statement, not in two different update queries. How can I do so? A query somewhat like this:

UPDATE tbl_test 
SET tag = 'a' where DBInstance in ('INS1') and DBName IN ('master'), 
tag = 'b' where DBInstance in ('INS2') and DBName IN ('tempdb')

But obviously, this query is wrong, so how can I do so?

Upvotes: 1

Views: 116

Answers (1)

Rahul Tripathi
Rahul Tripathi

Reputation: 172408

You can use the case when then like this:

UPDATE tbl_test 
SET tag = case 
            when DBInstance = 'INS1' and DBName = 'master'
              then 'a'
            when DBInstance = 'INS2' and DBName = 'tempdb'
              then 'b'
            else NULL -- or may be tag, or default value which you want.
          end

Upvotes: 3

Related Questions