mysqlsqlif-statementconditional-statementsgenerated-columns

Generated columns with condition/if else/case to populate the new column mysql

I have a column-City, i want to create a new column - Metro, i want it to check if city in('x','y','z') then Metro='Metro' else 'Non metro'

   ALTER TABLE sales ADD COLUMN Metro_city VARCHAR(45) GENERATED ALWAYS AS  (IF (CITY in('Mumbai ','Delhi' , 'Bangalore') then Metro_city='Metro') ('Non-Metro' end if)) STORED;

i also have another set of values to put into case ,when then but i am guessing if i make this work , that will too. i want it to store into the table and not a select query

Solution

  • You need to fix the case expression, basically:

    ALTER TABLE sales ADD COLUMN Metro_city VARCHAR(45) GENERATED ALWAYS AS
      (case when CITY in ('Mumbai ', 'Delhi' , 'Bangalore')
            then 'Metro'
            else 'Non-Metro'
       end ) STORED;