Replacing birth year column values with calculated age value

Question

I have a stored procedure that is taking more than 2 hours to complete and I noticed the below SQL is taking close to 25 mins for updating a million+ values. There are multiple steps like this and all added up takes around 2+ hours to finish.

All it is doing is taking a column value i.e. birth_year and replacing it with age i.e. getdate() - birth_year.

SELECT     id,
           birth_month,
           birth_year
INTO       #birth_date
FROM       table_name
WHERE      birth_year IS NOT NULL

UPDATE     table_name
SET        birth_year = ( YEAR(GETDATE()) - bd.birth_year )
FROM       #birth_date bd
INNER JOIN table_name ap ON ap.id= bd.id
WHERE      bd.birth_month <= MONTH(GETDATE())
           OR bd.birth_month IS NULL

UPDATE     table_name
SET        birth_year = ( YEAR(GETDATE()) - bd.birth_year - 1 )
FROM       #birth_date bd
INNER JOIN table_name ap ON ap.id= bd.id
WHERE      bd.birth_month > MONTH(GETDATE())

I replaced the above SQL with the below SQL and now instead of 25 mins, the below SQL takes less than a minute. I've verified the results and all seems good. Is there anything that I am missing? Is there a better or alternate way?

UPDATE  table_name
SET     birth_year = case
                        when birth_month <= MONTH(GETDATE())  OR birth_month IS NULL then (YEAR(GETDATE()) - birth_year)
                        when birth_month > MONTH(GETDATE()) then ( YEAR(GETDATE()) - birth_year - 1 )
                        else null
                     end
FROM    table_name

Answer 1

First, as dnoeth commented, why are you doing this job in mass rather than calculating on the fly? You might have a good reason, but you never clarified. Of course the best way to lighten the load would be not to do this task at all.

Second, as 200_success commented, you are damaging your database design by writing age data into a birth_year column.

If you MUST run this procedure and you MUST overwrite the birth_year, then I have some advice for your single-query approach:

1. You should consistently use all caps when writing mysql keywords and functions for readability.
2. You should try to minimize the number function calls and overall length of your query.
3. Because, I assume, you have covered all potential outcomes with your table data in your CASE conditions, there should be no logical requirement for the null outcome. Furthermore, because there are only 2 outcomes, I would opt for an IF statement rather than the more verbose CASE statement.
4. Depending on the possibility of a null value in birth_year, you may need to include another IF or COALESCE call to mitigate that data challenge.

SQL (D.R.Y.):

UPDATE table_name
SET birth_year = YEAR(GETDATE()) - birth_year - IF(birth_month IS NULL OR birth_month <= MONTH(GETDATE()), 0, 1) 
FROM table_name

Answer 2

As per my understanding, you are looking for current age for each records. But for that, you should go ahead with datedifference function by year. no need to calculate this much operation.
Make sure you should have index on below columns.
birth_month , birth_year

After all, please check and let us know the execution time readings.