can I add values into a column conditionally in mysql via a query (heidisql)?

Question

I'm having trouble inserting values conditionally into a column via a query. I am trying to add the following: low, med, high into a new column called income_level which is based on the value in column income. What i wish to query:

INSERT INTO chile

VALUE ('low_income' if income < 10000, 'medium_income' if income BETWEEN 10000 AND 100000, 'high_income' if income >100000)

Current state of the table

ID	income
1	7000
2	15000

Wished state of the table

ID	Income	Income_level
1	7000	low_income
2	15000	medium_income
3	110000	high_income

I have been searching around stackoverflow and searching for an answer but haven't found a applicable solution that I could've used.

Answer 1

I suggest making income_level a generated column, so you don't have to insert a value. Also you don't have to worry if you update the income that the income_level becomes out of sync.

ALTER TABLE chile ADD COLUMN income_level VARCHAR(20)
  GENERATED ALWAYS AS (CASE WHEN income < 10000 THEN 'low_income'
                            WHEN income > 100000 THEN 'high_income'
                            ELSE 'medium_income' END);