sql server updating new column based on another column from same table

Question

I have a table with composite primary key based on three columns.

(Unit, Account, service)

I have another two columns: DOB (this holds date of birth) & Age (this is a new int column which needs to be updated based on DOB and the result will be integer value of years)

I know how to retrieve the reslt for Age

select datediff(Year,DOB,GETDATE()) as AGE

But not sure how to update entire table based on the rows DOB data.

Columns are Unit, Account, Service, DOB, Age

Your age formula won't work. Try `DATEDIFF(year,'2010-12-1',getdate())` and see. — podiluska, Oct 26 '12 at 14:16
Age is ever changing...In your case, you have to update the Age column everyday. Instead calculate the Age whenever you need it. — Kaf, Oct 26 '12 at 14:21

score 2 · Answer 1 · edited May 23 '17 at 12:12

2

As per the comments, it isn't wise to persist Age as it varies daily (and possibly more frequently, if you have users in different timezones).

Also, your Age vs DOB algorithm isn't accurate - see here for a better one

As a consequence, IMO this is one scenario where a non-persisted COMPUTED column makes sense, like so:

ALTER TABLE Person add Age 
AS DateDiff(yy,DOB,CURRENT_TIMESTAMP) 
- CASE WHEN DATEPART  (mm,DOB) <= DATEPART(mm,CURRENT_TIMESTAMP) 
       and DATEPART(dd, DOB) <= DATEPART(dd,CURRENT_TIMESTAMP)
    THEN 0 
    ELSE 1
  END

edited May 23 '17 at 12:12

Community

1
1

answered Oct 26 '12 at 14:32

StuartLC

104,537
17
209
285

I am running an SSIS package and boss has given me the task to enter the age based on the day package has run. The age column will be valid at the time of the transaction and that is what they are looking for. – Asghar Oct 26 '12 at 14:37

Kermit · Answer 2 · 2012-10-26T14:33:19.743

1

Get rid of the age column and calculate the age like so:

SELECT DATEDIFF(yy, DOB, getdate()) AS age FROM daffyduck

There are very rare cases that you need to store items such as age. Since age changes daily, you will have to update your records often. It is instead better to store a fixed value, such as the date of birth that can be calculated. Most DBMS's provide the functionality for doing the date arithmetic for this reason.

edited Oct 26 '12 at 14:33

answered Oct 26 '12 at 14:28

Kermit

33,827
13
85
121

1

Of course, this could be added as a *computed* column, but as @podiluska points out, the OP needs to get the formula right first. – Damien_The_Unbeliever Oct 26 '12 at 14:32
@JonathanLeffler I don't see a negative age [here](http://sqlfiddle.com/#!3/d41d8/5589). – Kermit Oct 26 '12 at 14:37
DateDiff(yy, '31-Dec-2011', '01-Jan-2012') One day old reported as one year old... – Laurence Oct 26 '12 at 14:39
@Laurence Ahh I see what you did there. – Kermit Oct 26 '12 at 14:40
We will not be updating age columns after the first update. They want the age on the day of transaction and it will not change in the future. – Asghar Oct 26 '12 at 14:41
@Asghar Then calculate it in the transaction. – Kermit Oct 26 '12 at 14:42
This is what I will be doing going forward i.e. calculating it in the transaction. – Asghar Oct 26 '12 at 17:27

score 1 · Accepted Answer · edited Oct 26 '12 at 15:15

1

To answer your question:

UPDATE dob.MyTable SET Age = datediff(Year,DOB,GETDATE());

This will update the entire table as per your requirements.

However, I strongly recommend you look at all the other answers here. Especially the ones about the calculation error in above formula.

edited Oct 26 '12 at 15:15

marc_s

732,580
175
1,330
1,459

answered Oct 26 '12 at 14:50

Sebastian Meine

11,260
29
41

Thank you Sebastian and everyone who replied. I am certainly checking the formula and correcting it as you and others have mentioned. Thank you for pointing this up. – Asghar Oct 26 '12 at 15:59

sql server updating new column based on another column from same table

3 Answers3