CODEWITHSUNDEEP
mysqldatabase
bbi
bbi

Reputation: 5

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

I have a table 1 with 16 unique values considering columns A, B and 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 another table, Table 2 with millions of rows and with the same structure of columns as Table 1 (except the id.periodo), so my 16 combinations from Table 1 repeats a lot in Table 2, however I do not have a id.periodo column in Table 2 to link it with table 1.

I would like to insert in Table 2 the column id.periodo following the same "matches" as Table 1. Of course it is not going to be a unique index, since the numbers from 1 to 16 will repeat a lot, but my intention is to create foreign key in Table 2 following the Primary Key (and also index) from Table 1.

Thank you in advance,

Gabriel

Upvotes: 0

Views: 43

Answers (1)

conceptdeluxe
conceptdeluxe

Reputation: 3893

You can update your table2 with the id_periodo field from monitor_periodo using following statement:

UPDATE
    table2
LEFT JOIN
    monitor_periodo
ON
    monitor_periodo.ano = table2.ano
AND
    monitor_periodo.mes = table2.mes
AND
    monitor_periodo.data = table2.data
SET
    table2.id_periodo = monitor_periodo.id_periodo
;

Then you can create the foreign key constraint with:

ALTER TABLE table2
    ADD FOREIGN KEY (id_periodo) REFERENCES monitor_periodo(id_periodo)
;

Upvotes: 1

Related Questions