By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,805 Members | 1,270 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,805 IT Pros & Developers. It's quick & easy.

How to create a computed column that is recalculated each time it is accessed

P: 3
Hello!
Considering a two column table
-------------------------
| DOB | Age |
-------------------------
|yyyy-mm-dd | Xy-Yd-Zd |
-------------------------
| | |

Using a UDF, I would like to force calculate Age every time the record is accessed.

Sorry if it is not clear.
Thanks in advance!
Sep 8 '13 #1

✓ answered by Rabbit

Create a UDF if you need to reuse code. Create a view if you want to display data in a specific way. Combine the two if you need both. In your case, you definitely want a view. Whether or not you create a UDF to use in the view is up to you.

Share this Question
Share on Google+
5 Replies


Expert 100+
P: 1,035
Expand|Select|Wrap|Line Numbers
  1. delimiter //
  2. CREATE PROCEDURE update_dob()
  3. BEGIN
  4.     UPDATE dob set age=datediff(dob,now());
  5. END//
  6.  
  7. delimiter ;
But this requires to run this procedure to update the values....
Expand|Select|Wrap|Line Numbers
  1.  call update_dob();
Sep 8 '13 #2

P: 3
Thank you Luuk.
I was hoping that there was I proper calculated/computed column type in Mysql.
Sep 8 '13 #3

Expert 100+
P: 1,035
maybe you can use a view
Expand|Select|Wrap|Line Numbers
  1. create view v_dob as select dob, datediff(dob,now()) age from dob;
  2.  
after doing "insert into dob (dob) values ('2013-09-01');"
Expand|Select|Wrap|Line Numbers
  1. [test@test]> select * from v_dob where dob='2013-09-01';
  2. +------------+------+
  3. | dob        | age  |
  4. +------------+------+
  5. | 2013-09-01 |   -7 |
  6. +------------+------+
  7. 1 row in set (0.00 sec)
  8.  
Sep 8 '13 #4

P: 3
Thank you again.
But I definitely need to use a UDF or something that allows me to return the age in this format :
x_years-y_month-z_days
eg : 10Y-11M-29D

Cheers!
Sep 8 '13 #5

Rabbit
Expert Mod 10K+
P: 12,364
Create a UDF if you need to reuse code. Create a view if you want to display data in a specific way. Combine the two if you need both. In your case, you definitely want a view. Whether or not you create a UDF to use in the view is up to you.
Sep 9 '13 #6

Post your reply

Sign in to post your reply or Sign up for a free account.