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: 
GMB's solution is 100% correct but I still wouldn't do it that way. You don't need the extra column income\_level because you can dynamically select it. This way you will also avoid the need of updating the redundant column when the definition of income levels changes due to e.g. inflation or other causes.

So this year your query might be this:

```
SELECT CASE WHEN income > 100000 THEN 'High income'
            WHEN income >  10000 THEN 'Middle income'
            ELSE 'Low income' 
        END AS income_level
FROM chile

```

In five years from now it might look like this:

```
SELECT CASE WHEN income > 150000 THEN 'High income'
            WHEN income >  15000 THEN 'Middle income'
            ELSE 'Low income' 
        END AS income_level
FROM chile

```

UPDATE: if income may be null and you don't want to consider this to be 'Low income' you can do this:

```
SELECT CASE WHEN income IS NULL  THEN 'Unknown income level'
            WHEN income > 100000 THEN 'High income'
            WHEN income >  10000 THEN 'Middle income'
            ELSE 'Low income' 
        END AS income_level
FROM chile

```
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.