kiwisan kiwisan - 7 months ago 10
SQL Question

MySQL: lookup multiple foreign key values before insert

I have a temporary table

tmp_c
of new info that I need to insert into the
metric
table:

mysql> select host, metric, value from tmp_c limit 2;
+----------+----------+--------+
| host | metric | value |
+----------+----------+--------+
| host1 | % | 86 |
| host2 | Kbs | 6529 |
+----------+----------+--------+

mysql> select * from hostID limit 1;
+----+--------------+
| id | name |
+----+--------------+
| 1 | host1 |
+----|--------------+

mysql> select * from metricID limit 1;
+----+------------+
| id | metric |
+----+------------+
| 1 | % |
+-----------------+

mysql> select id, hostID, metricID, value from metric limit 1;
+----------+----------+-----------+---------+
| id | hostID | metricID | value |
+----------+----------+-----------+---------+
| 1 | 1 | 1 | 86 |
| 2 | 1 | 2 | 7765 |
+----------+----------+-----------+---------+


How can I look up the foreign keys for hostID and metricID, and insert the data from
tmp_c
into
metric
?

I got it working using the below for another query in different tables using only one foreign key relationship: (but can't get it to work using two in this case). Maybe use a view?

INSERT IGNORE into host(cluster_id, name, numcpu, cpu_tot_mhz, mem_tot)
SELECT c.id as cluster_id, t.Entity as name, t.numcpu as numcpu,
t.CpuTotalMhz as cpu_tot_mhz, `Total Memory` as mem_tot from tmp_c t
INNER JOIN cluster c on c.name = t.Cluster;

Answer

Just use two joins:

insert into metric(hostid, metricid, value)
    select h.id, m.id, c.value
    from tmp_c c join
         host h
         on c.host = h.name join
         metricid m
         on c.metric = m.metric;

This assumes that the id is auto incremented, so it doesn't need to be passed in.