jmsf jmsf - 2 months ago 6
MySQL Question

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.

Answer

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'