Question: I'm new to MySQL and I'm currently trying to learn to become more efficient in it. In this case, I have a database that has people's incomes and I want to create a new column `income_level` that says low, middle or high income based on the income. I have done this in four queries, but I'm sure that it could be done more efficiently? Could someone give me tips to this? Thank you!

Here are my queries so far:

```
ALTER TABLE Chile
ADD COLUMN `income_level` VARCHAR(50) NULL DEFAULT NULL AFTER `income`;

UPDATE Chile SET income_level = "Low income"
WHERE income < 10000;

UPDATE Chile SET income_level = "Middle income"
WHERE income > 10000;

UPDATE Chile SET income_level = "High income"
WHERE income > 100000;

```

Answer: 
Use a `case` expression:

```
update chile
set income_level = case
    when income > 100000 then 'High income'
    when income >  10000 then 'Middle income'
    else 'Low income'
end

```

If `income` may be `null` and you don't want to update the corresponding rows, then you can add a `where` clause to the query.
Actually, although GMB's answer is correct, I disagree rather strongly with rf1234. You don't want to implement this logic in `SELECT` queries, because the logic gets spread across too much code. You *could* use a view, but there is a better solution.

The best approach for calculating `income_level` in my opinion is to use a generated column:

```
alter table chile
    add income_level varchar(255) generated always as
        (case when income > 100000 then 'High income'
              when income >  10000 then 'Middle income'
              else 'Low income'
         end);

```

Why is this an improvement? The `income_level` and `income` columns are *always* in synch, because the `income_level` is calculated when it is retrieved. So, if you add new rows or update existing rows, then the value automatically changes.