Use Case statement to update table

Question

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.

Answer 1

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'