mysql - fill field by other fields

I have a birthdate, year, month, day columns w开发者_开发百科here columns "year,month,day" are foreign key to other tables

What I want to do is for each birthdate get id(year(birthdate)) to be the value of year column and the same thing for month and day columns.

How can I do this in MySQL?

Thanks in advance

Check out triggers (http://dev.mysql.com/doc/refman/5.0/en/triggers.html) so every time the data is written you calculate and update the information you require.

Update mytable
set birthdate = Yearfield + monthfield + dayfield

EDIT

If birthdate is a date field you will have to cast or convert it first.

Update mytable
    set birthdate = Cast(Yearfield + monthfield + dayfield as date)

Try

update tableName set birthdate = month + "/" + day + "/" + year;