1

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?

Hemant Sisodia
  • 488
  • 6
  • 23
  • possible duplicate of [T-SQL: Using a CASE in an UPDATE statement to update certain columns depending on a condition](http://stackoverflow.com/questions/4830191/t-sql-using-a-case-in-an-update-statement-to-update-certain-columns-depending-o) – Tony Apr 28 '15 at 10:35
  • @Tony is right, just use CASE statement, like `set tag CASE WHEN .....END`. – MacKentoch Apr 28 '15 at 10:37

1 Answers1

3

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
freefaller
  • 19,368
  • 7
  • 57
  • 87
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • @RahulTripathi - yes, I deleted my comment as I realised they were already null... but I thought I would mention it anyway, just in case anybody deleted values without realising – freefaller Apr 28 '15 at 10:50