sql group update column by age range-CodePudding

I want to update the class column of 33-year-olds as 2, 34-35-year-olds class column as 3, 36-year-olds class column as 4. I'm using Microsoft SQL Server.

ID DATE_OF_BIRTH CLASS
1  10.10.1986    1
2  11.02.1987    2
2  18.04.1988    3
.....................
.....................
.....................

CodePudding user response：

UPDATE YourTableName
SET CLASS = CASE WHEN DATEDIFF(year, DATE_OF_BIRTH, GETDATE()) = 33 THEN 2
                 WHEN DATEDIFF(year, DATE_OF_BIRTH , GETDATE())  BETWEEN 34 AND 35 THEN 3
                 WHEN DATEDIFF(year, DATE_OF_BIRTH , GETDATE()) = 36 THEN 4
                 ELSE CLASS
            END

CodePudding user response：

You could update the table with a case expression over the calculation of the person's age:

UPDATE mytable
SET    class = CASE DATEDIFF(YEAR, date_of_birth, GETDATE()) 
                    WHEN 33 THEN 2
                    WHEN 34 THEN 3
                    WHEN 35 THEN 3
                    WHEN 36 THEN 4
                    ELSE class -- safeguard
               END