Re: [php] Foreign Keys erzeugen

[Kristian Köhntopp <kk_(at)_netuse.de>]

Stephan Huber wrote:
> update host set host.id=(select id from dom where
> host.hostname like concat("%.", dom.domain))

mysql> update host
    ->   set host.id = (
    ->     select id from dom
    ->         where host.hostname like concat("%.", dom.domain)
    -> );
ERROR 1064: parse error near 'select id from dom
        where host.hostname like concat("%.", dom.domain)
)' at line 3

geht nicht. Aber

mysql> create table tmp_host (
    ->   ip varchar(16),
    ->   hostname varchar(127),
    ->  id integer);
Query OK, 0 rows affected (0.07 sec)

mysql> 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);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> drop table host;
Query OK, 0 rows affected (0.05 sec)

mysql> alter table tmp_host rename host;
Query OK, 0 rows affected (0.09 sec)

mysql> select * from host;
+--------------+----------------------+------+
| ip           | hostname             | id   |
+--------------+----------------------+------+
| 193.98.110.1 | nuki.netuse.de       |    1 |
| 193.102.57.4 | white.koehntopp.de   |    2 |
| 192.102.57.3 | valiant.koehntopp.de |    2 |
+--------------+----------------------+------+
3 rows in set (0.00 sec)

Dank an Christian Mack (mack_(at)_compal.de) auf der MySQL Liste.

Kristian

-- 
Kristian Köhntopp, NetUSE Kommunikationstechnologie GmbH
Siemenswall, D-24107 Kiel, Germany, +49 431 386 436 00
Using PHP3? See our web development library at
http://phplib.shonline.de/ (GPL)