CODEWITHSUNDEEP
mysqlsqlcase
jmsf
jmsf

Reputation: 35

Use Case statement to update table

UPDATE crm_accounts 
SET 
    reg = "Sim" 
WHERE age >= 17 
AND age <= 35 
AND balance > 0.00
AND type = "Júnior";


UPDATE crm_accounts 
SET 
    reg = "Não" 
WHERE age >= 17 
AND age <= 35 
AND balance = 0.00
AND type = "Júnior";


UPDATE crm_accounts 
SET 
    type = "Efetivo"
WHERE age >= 17
AND age <= 35 
AND type = "Júnior";

The statement work for the purpose but i want to simplify and combine in a case statement. Any help? Thanks.

Upvotes: 3

Views: 402

Answers (2)

Naresh Kumar P
Naresh Kumar P

Reputation: 4210

CASE Statements in MYSQL

There are two syntax for CASE Statements

SYNTAX 1:

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

SYNTAX 2:

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

Explanation on Syntax One:

For the first syntax, case_value is an expression. This value is compared to the when_value expression in each WHEN clause until one of them is equal. When an equal when_value is found, the corresponding THEN clause statement_list executes. If no when_value is equal, the ELSE clause statement_list executes, if there is one.

This syntax cannot be used to test for equality with NULL because NULL = NULL is false.

Explanation on Syntax Two:

For the second syntax, each WHEN clause search_condition expression is evaluated until one is true, at which point its corresponding THEN clause statement_list executes. If no search_condition is equal, the ELSE clause statement_list executes, if there is one.

If no when_value or search_condition matches the value tested and the CASE statement contains no ELSE clause, a Case not found for CASE statement error results.

Each statement_list consists of one or more SQL statements; an empty statement_list is not permitted.

To handle situations where no value is matched by any WHEN clause, use an ELSE containing an empty BEGIN ... END block, as shown in this example. (The indentation used here in the ELSE clause is for purposes of clarity only, and is not otherwise significant.) 

DELIMITER |

CREATE PROCEDURE p()
  BEGIN
    DECLARE v INT DEFAULT 1;

    CASE v
      WHEN 2 THEN SELECT v;
      WHEN 3 THEN SELECT 0;
      ELSE            
        END;
    END CASE;
  END;
  |

And your Code can be Updated using the CASE Statements like this..

UPDATE `crm_accounts` SET `reg` = CASE

WHEN age >= 17 
AND age <= 35 
AND balance > 0.00
AND type = "Júnior" THEN "Sim"

WHEN age >= 17 
AND age <= 35 
AND balance = 0.00
AND type = "Júnior" THEN "Não"

WHEN age >= 17
AND age <= 35 
AND type = "Júnior" THEN "Efetivo"

ELSE `reg`

END

Upvotes: 0

spencer7593
spencer7593

Reputation: 108410

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'

Upvotes: 2

Related Questions