T-Sql: Using a Case in an Update Statement to Update Certain Columns Depending on a Condition

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.

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')

How to update column values depending on another column's values with CASE WHEN in SQL

You want to update the table
use

UPDATE groovers
SET profileImage = CASE WHEN gender = 'male' THEN 'imgM'
                        WHEN gender = 'female' THEN 'imgF'
                        ELSE 'default' END;

Schema (MySQL v5.7)

CREATE TABLE groovers
(
    user_id         INT AUTO_INCREMENT PRIMARY KEY,
    username        VARCHAR(40)                                                  NOT NULL,
    firstName       VARCHAR(40)                                                  NOT NULL,
    lastName        VARCHAR(40)                                                  NOT NULL,
    gender          enum ('Male', 'Female', 'Unspecified') default 'Unspecified' NOT NULL,
    email           VARCHAR(255)                                                 NOT NULL,
    password        VARCHAR(32)                                                  NOT NULL,
    profileImage    VARCHAR(255)                                                 NOT NULL
);

INSERT INTO groovers VALUES (0,'test','test','test','male','test@test.com','a',''),
(0,'test','test','test','female','test@test.com','a',''),
(0,'test','test','test','Unspecified','test@test.com','a','');

UPDATE groovers
SET profileImage = CASE WHEN gender = 'male' THEN 'imgM'
                        WHEN gender = 'female' THEN 'imgF'
                        ELSE 'default' END;

---

Query #1

SELECT * FROM groovers;

| user_id | username | firstName | lastName | gender      | email         | password | profileImage |
| ------- | -------- | --------- | -------- | ----------- | ------------- | -------- | ------------ |
| 1       | test     | test      | test     | Male        | test@test.com | a        | imgM         |
| 2       | test     | test      | test     | Female      | test@test.com | a        | imgF         |
| 3       | test     | test      | test     | Unspecified | test@test.com | a        | default      |

---

View on DB Fiddle

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

Updating multiple columns depending on 1 CASE-condition

A case statement only returns one value:

UPDATE someTable
    SET id = (CASE id WHEN 1 THEN 111 WHEN 2 THEN 222 WHEN 3 THEN 333 ELSE id END),
        name = (CASE id WHEN 1 THEN 'Dr. Frankenstein'
                        WHEN 2 THEN 'the Monster'
                        WHEN 3 THEN 'Mr. X'
                        ELSE name
                END)
    WHERE id IN (1, 2, 3);

For performance, be sure you an an index on id. This will help with finding the records to update. Do note that changing the id value requires updating the index, which can be a bit longer than a normal update. However, expecting 5-10 transactions a second is reasonable.

updating multiple columns using case statement in sql server

You'll have to swap the syntax around. The case statement will be applied for every value you want to update...

UPDATE table SET
    pay1 = CASE WHEN @columnname IN('name1') THEN pay1 * 100 ELSE pay1 END,
    pay2 = CASE WHEN @columnname IN('name1', 'name2') THEN pay2 * 20 ELSE pay2 END,
    pay3 = CASE WHEN @columnname IN('name1', 'name2', 'name3') THEN pay3 * 100 ELSE pay3 END

It looks like you actually want is a if statement....

IF @columnname = 'name1'
    UPDATE table SET pay1 = pay1 * 100, pay2=pay2*20, pay3=pay3* 100

ELSE IF @ColumnName = 'name2'
    UPDATE table SET pay2 = pay2 * 20, pay3 = pay3 * 100

ELSE IF @ColumnName = 'name3'
    UPDATE table SET pay3 = pay3 * 100

Hope that helps

Update fields based on If-Else condition

You can't conditionally update a column, you either have to update the column or not. But you can conditionally change the value using a case expression. Using your conditions you can either set the specified columns to new values OR keep the old values as follows.

update B set
  [Name] = case when B.check1 = 'Hello' and B.check2 in ('World','Pineapple') then A.[Name] else B.[Name] end
  , City = case when B.check1 = 'Hello' and B.check2 in ('World') then A.City else B.City end
  , Phone = case when B.check1 = 'Hello' and B.check2 in ('World') then A.Phone else B.Phone end
from TableB B
inner join TableA A on A.email = B.email;

Note: You can do the same thing with iif (its just shorthand for case), I prefer case.

UPDATE with WITH and CASE - PostgreSQL

The syntax error occurs because your co-related subquery isn't valid. You need to have some select statement after the two common table expressions:

The basic structure of a common table expression is this:

with ptn as (...),
  rc as (...)
select --<< you are missing this select here

But I think the whole thing can be written shorter and more efficiently (if I'm not mistaken)

UPDATE campaigns AS cmp
    SET name = CASE
                 WHEN rc.office IS NULL OR rc.office = '' THEN ptn.first_name || ' ' || ptn.last_name
                ELSE ptn.first_name || ' ' || ptn.last_name || ' for ' || rc.office
              END
from politicians ptn, races rc 
where ptn.id = cmp.politician_id
  and rc.id = cmp.race_id

Using a conditional UPDATE statement in SQL

I think what you want is:

UPDATE EMPLOYEE
SET age =
CASE WHEN AGE < 20 THEN 15
ELSE 20 END

---

Related Topics