How to update a column on insert based on a value

Question

I have a csv file with customer birth dates that looks like:

CUST_BIRTH_DT
1955-07-10

When I import the data into my table, I want MySQL to calculate the age and replace the actual birth date with the age.

Is that possible in MySQL? If so how do I do it?

I've looked on stack overflow already and tried possible things and I have had no luck.

@PM77-1 No, I don't think a stored proc is needed here at all. — Tim Biegeleisen, Mar 27 '17 at 02:03

score 0 · Answer 1 · answered Mar 27 '17 at 02:02

I started writing a solution which uses a function in LOAD DATA to convert the birthdate to an age, but then I stopped, because I don't think this is a good idea. Customer age is something which is always changing, getting larger in fact, as time progresses. This means that whatever age you set when reading in your data will become stale at some point, depending on the resolution of the age (e.g. years only, year/months/days, etc.).

So I would actually recommend generating the age when you query your table:

SELECT CUST_ID,
       DATEDIFF(NOW(), CUST_BIRTH_DT) AS age
FROM yourTable

Thats a really good point. Didn't really think of that. Thank you. — , Mar 27 '17 at 02:09

How to update a column on insert based on a value

1 Answers1