List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:May 11 1999 2:02pm
Subject:Re: Constructing a foreign key
View as plain text  
Kristian Köhntopp wrote:
> 
> I have the following sample tables:
> 
> #
> # Table structure for table 'dom'
> #
> CREATE TABLE dom (
>   id int(11),
>   domain varchar(127)
> );
> 
> #
> # Dumping data for table 'dom'
> #
> 
> INSERT INTO dom VALUES (1,'netuse.de');
> INSERT INTO dom VALUES (2,'koehntopp.de');
> 
> #
> # Table structure for table 'host'
> #
> CREATE TABLE host (
>   ip varchar(16),
>   hostname varchar(127),
>   id int(11)
> );
> 
> #
> # Dumping data for table 'host'
> #
> 
> INSERT INTO host VALUES ('193.98.110.1','nuki.netuse.de',0);
> INSERT INTO host VALUES ('193.102.57.4','white.koehntopp.de',0);
> INSERT INTO host VALUES
> ('192.102.57.3','valiant.koehntopp.de',0);
> 
> I need to load a large batch of external data into the "host"
> table. The value "id" in host is a foreign key into the dom table
> and can be calculated in many cases where host.hostname is a
> fully qualified domainname.
> 
> I already can do
> 
> mysql> select concat("%.", dom.domain) as bla from dom;
> +----------------+
> | bla            |
> +----------------+
> | %.netuse.de    |
> | %.koehntopp.de |
> +----------------+
> 2 rows in set (0.00 sec)
> 
> and I can do this, too:
> mysql> select dom.id, domain, hostname from dom, host where
> host.hostname like concat("%.", dom.domain);
> +------+--------------+----------------------+
> | id   | domain       | hostname             |
> +------+--------------+----------------------+
> |    1 | netuse.de    | nuki.netuse.de       |
> |    2 | koehntopp.de | white.koehntopp.de   |
> |    2 | koehntopp.de | valiant.koehntopp.de |
> +------+--------------+----------------------+
> 3 rows in set (0.01 sec)
> 
> which is exactly the relation I want to have. Alas, this query is
> slow (leading "%" makes indices useless) and so I want to
> calculate this relation only once and then set up the host.id
> values properly so I can use them as foreign keys for a fast
> join.
> 
> I tried
> 
> mysql> update host set host.id = dom.id
> > where host.hostname like concat("%.", dom.domain);
> ERROR 1109: Unknown table 'dom' in field list
> 
> which refers to the "host.id = dom.id" part of the update
> statement.
> 
> How do I set up the host.id values properly so that I can get
> quick joins with later queries?
> 
> Kristian
> 
> --
> Kristian Köhntopp, NetUSE Kommunikationstechnologie GmbH

Hi Kristian

1) create a temporary table with the same columns as host (e.g. tmp_host).
2) make an:
INSERT INTO 
	tmp_host 
	( ip
	, hostname
	, id 
	) 
SELECT
	host.ip
	, host.hostname
	, IFNULL( dom.id, 0)
FROM
	host
	LEFT JOIN dom
	ON host.hostname like CONCAT('%', dom.domain)

3) DROP TABLE host
4) ALTER TABLE tmp_host RENAME host

That's it :)

Tschau
Christian

Thread
Constructing a foreign keyKristian K√∂hntopp11 May
  • Re: Constructing a foreign keyChristian Mack11 May