mysql - add a value in table 1 from table 2 where multiple columns matches -

i have table 1 16 unique values considering columns a, b , c.

    create table `monitor_periodo` (   `ano` varchar(255) default 'null',   `mes` varchar(255) default 'null',   `data` varchar(255) default 'null',   `id_periodo` int(11) not null auto_increment,   primary key (`id_periodo`),   unique key `unique` (`ano`,`mes`,`data`) ) 

a have table, table 2 millions of rows , same structure of columns table 1 (except id.periodo), 16 combinations table 1 repeats lot in table 2, not have id.periodo column in table 2 link table 1.

i insert in table 2 column id.periodo following same "matches" table 1. of course not going unique index, since numbers 1 16 repeat lot, intention create foreign key in table 2 following primary key (and index) table 1.

thank in advance,

gabriel

you can update table2 id_periodo field monitor_periodo using following statement:

update     table2 left join     monitor_periodo on     monitor_periodo.ano = table2.ano ,     monitor_periodo.mes = table2.mes ,     monitor_periodo.data = table2.data set     table2.id_periodo = monitor_periodo.id_periodo ; 

then can create foreign key constraint with:

alter table table2     add foreign key (id_periodo) references monitor_periodo(id_periodo) ;