I Want to Use Case Statement to Update Some Records in SQL Server 2005

I want to use CASE statement to update some records in sql server 2005

Add a WHERE clause

UPDATE dbo.TestStudents  
SET     LASTNAME =  CASE  
                        WHEN LASTNAME = 'AAA' THEN 'BBB' 
                        WHEN LASTNAME = 'CCC' THEN 'DDD' 
                        WHEN LASTNAME = 'EEE' THEN 'FFF' 
                        ELSE LASTNAME
                    END 
WHERE   LASTNAME IN ('AAA', 'CCC', 'EEE')

T-SQL: Using a CASE in an UPDATE statement to update certain columns depending on a condition

You can't use a condition to change the structure of your query, just the data involved. You could do this:

update table set
    columnx = (case when condition then 25 else columnx end),
    columny = (case when condition then columny else 25 end)

This is semantically the same, but just bear in mind that both columns will always be updated. This probably won't cause you any problems, but if you have a high transactional volume, then this could cause concurrency issues.

The only way to do specifically what you're asking is to use dynamic SQL. This is, however, something I'd encourage you to stay away from. The solution above will almost certainly be sufficient for what you're after.

Use Case statement to update table

It looks like you want something like this:

 UPDATE crm_accounts a
    SET a.reg 
      = CASE
          WHEN a.balance > 0.00 THEN 'Sim'
          WHEN a.balance = 0.00 THEN 'Não'
          ELSE a.reg
        END
      , a.type = 'Efetivo'
  WHERE a.age >= 17
    AND a.age <= 35
    AND a.type = 'Júnior'

---

Whenever I'm writing update statements like this, I always test the expressions and predicates in a SELECT statement first, and verify the results. I make sure everything is working the way I need it to before I convert it to an UPDATE statement.

 SELECT a.age >= 17
      , a.type
      , a.reg AS old_reg
      , CASE
          WHEN a.balance > 0.00 THEN 'Sim'
          WHEN a.balance = 0.00 THEN 'Não'
          ELSE a.reg
        END AS new_reg
   FROM crm_accounts a
  WHERE a.age >= 17
    AND a.age <= 35
    AND a.type = 'Júnior'

Using case statement in update query

Just to add a slightly different variant that I tend to prefer (down to personal preference).

UPDATE Person
SET Name = Name + CASE WHEN Name LIKE 'S%' THEN '1' ELSE '2' END

I like this because it saves repeating the "Name +" bit for each condition - in this case it's nothing major, but in other scenarios with more conditions it can be overly repetitive

Use CASE to update some records

MS Access' flavor of SQL does not support CASE expressions. You could use IIF() here instead:

UPDATE Table1
SET Test = IIF(Test = "A", "B", "A")
WHERE Test IN ("A", "B");

UPDATE - CASE Statement - Way too many results

With the structure you have, you still update all the records that meet the criteria in WHERE clause. For those that are not specified in CASE, it will simply set NULL.

What you should do is to specify all records with idworkitem that you want to update in WHERE clause.

Update Statement with Case and DateDiff

You must drop the select statement inside case:

DECLARE @X INT

UPDATE MyTable
SET @X = DATEDIFF(s, Start_Date, End_Date)
    ,Column1 = CASE 
        WHEN Start_Date is not NULL AND End_Date is not NULL
            THEN    CONVERT(VARCHAR(10), (@x / 86400)) + ' Days ' + 
                    CONVERT(VARCHAR(10), ((@x % 86400) / 3600)) + ' Hours ' + 
                    CONVERT(VARCHAR(10), (((@x % 86400) % 3600) / 60)) + ' Minutes '
        ELSE NULL
    END

It seems that the subquery

(SELECT CONVERT....) 

is evaluated before

@X = DATEDIFF(s, Start_Date, End_Date)

so @X is still null and the result of the subquery is null.

Case statement in SQL Server 2005

Suggest a structure of IF and ELSE IF to mimic a switch.

IF @MyVar = 'Foo'
BEGIN
    --react to Foo        
END

ELSE IF @MyVar = 'Bar'
BEGIN
    --react to Bar       
END
ELSE
BEGIN
    --default case.
END

---

Related Topics