How to add a column fill with values to the table in MYSQL-CodePudding

I have table which has a column age. I categorized this age column like this way

select 
CASE WHEN age>60 THEN 'old'
WHEN age between 30 and 60 THEN 'middled age'
WHEN age between 20 and 30 THEN 'young'
WHEN age between 13 and 20 THEN 'teen'
else 'kid'
end as age_category
from customer

Now i want this age_category to add to customer table. So I created an empty column

ALTER table customer add column age_category varchar;

The next I want to fill it up with values, so i tried to add the output of first statement using insert command like the following way

INSERT INTO customer (age_category) values
(SELECT
CASE WHEN age>60 THEN 'old'
WHEN age between 30 and 60 THEN 'middled age'
WHEN age between 20 and 30 THEN 'young'
WHEN age between 13 and 20 THEN 'teen'
ELSE'kid'
END as age_category
FROM customer)

but it did not worked

CodePudding user response：

I think you want an update here, not an insert:

UPDATE customer
SET age_category = CASE WHEN age >= 60 THEN 'old'
                        WHEN age >= 30 THEN 'middle aged'
                        WHEN age >= 20 THEN 'young'
                        WHEN age >= 13 THEN 'teen'
                        ELSE 'kid' END;

However, given that age_category is derived data, you might want to consider not storing this as a column. The reason is that anytime the age data should change (via update/insert) you would have to regenerate the age_category values. Another option, if you're using MySQL 8 , might be to use a generated column.

CodePudding user response：

Add generated column.

ALTER TABLE customer 
ADD COLUMN age_category VARCHAR(255) 
    GENERATED ALWAYS AS ( CASE WHEN age > 60 THEN 'old'
                               WHEN age > 29 THEN 'middle aged'
                               WHEN age > 19 THEN 'young'
                               WHEN age > 12 THEN 'teen'
                               ELSE 'kid' 
                               END );

If age column value changes then the value of this generated column changes automatically.