はじめに

MySQLで複数のテーブルをUpdate、Deleteする方法を記述します。

環境

MySQL 5.5

サンプルデータ

mysql> show tables;
+------------------+
| Tables_in_sample |
+------------------+
| frameworks       |
| languages        |
+------------------+
2 rows in set (0.00 sec)

mysql> desc languages;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc frameworks;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id          | int(11)      | YES  |     | NULL    |       |
| language_id | int(11)      | YES  |     | NULL    |       |
| name        | varchar(255) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from languages;
+------+--------+
| id   | name   |
+------+--------+
|    1 | PHP    |
|    2 | Java   |
|    3 | Ruby   |
|    4 | Python |
|    5 | Perl   |
+------+--------+
5 rows in set (0.00 sec)

mysql> select * from frameworks;
+------+-------------+------------------+
| id   | language_id | name             |
+------+-------------+------------------+
|    1 |           1 | Laravel          |
|    2 |           1 | Symfony          |
|    3 |           1 | CodeIgniter      |
|    4 |           1 | CakePHP          |
|    5 |           1 | Zend Framework   |
|    6 |           2 | Play Framework   |
|    7 |           2 | Spring Framework |
|    8 |           2 | Apache Struts    |
|    9 |           3 | Ruby on Rails    |
|   10 |           3 | Sinatra          |
|   11 |           3 | Padrino          |
|   12 |           4 | Django           |
|   13 |           4 | Flask            |
|   14 |           4 | Tornado          |
|   15 |           5 | Mojolicios       |
|   16 |           5 | Dancer           |
|   17 |           5 | Catalyst         |
+------+-------------+------------------+
17 rows in set (0.00 sec)

mysql> select
    ->   l.id language_id
    ->   , l.name language_name
    ->   , f.id framework_id
    ->   , f.name framework_name
    -> from
    ->   languages l
    ->   inner join frameworks f
    ->     on l.id = f.language_id;
+-------------+---------------+--------------+------------------+
| language_id | language_name | framework_id | framework_name   |
+-------------+---------------+--------------+------------------+
|           1 | PHP           |            1 | Laravel          |
|           1 | PHP           |            2 | Symfony          |
|           1 | PHP           |            3 | CodeIgniter      |
|           1 | PHP           |            4 | CakePHP          |
|           1 | PHP           |            5 | Zend Framework   |
|           2 | Java          |            6 | Play Framework   |
|           2 | Java          |            7 | Spring Framework |
|           2 | Java          |            8 | Apache Struts    |
|           3 | Ruby          |            9 | Ruby on Rails    |
|           3 | Ruby          |           10 | Sinatra          |
|           3 | Ruby          |           11 | Padrino          |
|           4 | Python        |           12 | Django           |
|           4 | Python        |           13 | Flask            |
|           4 | Python        |           14 | Tornado          |
|           5 | Perl          |           15 | Mojolicios       |
|           5 | Perl          |           16 | Dancer           |
|           5 | Perl          |           17 | Catalyst         |
+-------------+---------------+--------------+------------------+
17 rows in set (0.00 sec)

複数テーブルをUpdateする

Javaのframeworkのみ'-----'にUpdateする場合

mysql> update
    ->   languages l,
    ->   frameworks f
    -> set
    ->   f.name = '-----'
    -> where
    ->   l.id = f.language_id
    ->   and l.name = 'Java';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select
    ->   l.id language_id
    ->   , l.name language_name
    ->   , f.id framework_id
    ->   , f.name framework_name
    -> from
    ->  languages l
    ->  inner join frameworks f
    ->    on l.id = f.language_id;
+-------------+---------------+--------------+----------------+
| language_id | language_name | framework_id | framework_name |
+-------------+---------------+--------------+----------------+
|           1 | PHP           |            1 | Laravel        |
|           1 | PHP           |            2 | Symfony        |
|           1 | PHP           |            3 | CodeIgniter    |
|           1 | PHP           |            4 | CakePHP        |
|           1 | PHP           |            5 | Zend Framework |
|           2 | Java          |            6 | -----          |
|           2 | Java          |            7 | -----          |
|           2 | Java          |            8 | -----          |
|           3 | Ruby          |            9 | Ruby on Rails  |
|           3 | Ruby          |           10 | Sinatra        |
|           3 | Ruby          |           11 | Padrino        |
|           4 | Python        |           12 | Django         |
|           4 | Python        |           13 | Flask          |
|           4 | Python        |           14 | Tornado        |
|           5 | Perl          |           15 | Mojolicios     |
|           5 | Perl          |           16 | Dancer         |
|           5 | Perl          |           17 | Catalyst       |
+-------------+---------------+--------------+----------------+
17 rows in set (0.00 sec)

perl及びそのframeworkを'*****'にupdateする場合

mysql> update
    ->   languages l,
    ->   frameworks f
    -> set
    ->   l.name = '*****'
    ->   , f.name = '*****'
    -> where
    ->   l.id = f.language_id
    ->   and l.name = 'Perl';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> select
    ->   l.id language_id
    ->   , l.name language_name
    ->   , f.id framework_id
    ->   , f.name framework_name
    -> from
    ->  languages l
    ->  inner join frameworks f
    ->    on l.id = f.language_id;
+-------------+---------------+--------------+----------------+
| language_id | language_name | framework_id | framework_name |
+-------------+---------------+--------------+----------------+
|           1 | PHP           |            1 | Laravel        |
|           1 | PHP           |            2 | Symfony        |
|           1 | PHP           |            3 | CodeIgniter    |
|           1 | PHP           |            4 | CakePHP        |
|           1 | PHP           |            5 | Zend Framework |
|           2 | Java          |            6 | -----          |
|           2 | Java          |            7 | -----          |
|           2 | Java          |            8 | -----          |
|           3 | Ruby          |            9 | Ruby on Rails  |
|           3 | Ruby          |           10 | Sinatra        |
|           3 | Ruby          |           11 | Padrino        |
|           4 | Python        |           12 | Django         |
|           4 | Python        |           13 | Flask          |
|           4 | Python        |           14 | Tornado        |
|           5 | *****         |           15 | *****          |
|           5 | *****         |           16 | *****          |
|           5 | *****         |           17 | *****          |
+-------------+---------------+--------------+----------------+
17 rows in set (0.00 sec)

複数テーブルをDeleteする

PHPとそのframeworkをdeleteする場合

mysql> delete
    ->   l, f
    -> from
    ->   languages l
    ->   inner join frameworks f
    ->   on l.id = f.language_id
    -> where
    ->   l.name = 'PHP';
Query OK, 6 rows affected (0.00 sec)

mysql> select
    ->   l.id language_id
    ->   , l.name language_name
    ->   , f.id framework_id
    ->   , f.name framework_name
    -> from
    ->  languages l
    ->  inner join frameworks f
    ->    on l.id = f.language_id;
+-------------+---------------+--------------+----------------+
| language_id | language_name | framework_id | framework_name |
+-------------+---------------+--------------+----------------+
|           2 | Java          |            6 | -----          |
|           2 | Java          |            7 | -----          |
|           2 | Java          |            8 | -----          |
|           3 | Ruby          |            9 | Ruby on Rails  |
|           3 | Ruby          |           10 | Sinatra        |
|           3 | Ruby          |           11 | Padrino        |
|           4 | Python        |           12 | Django         |
|           4 | Python        |           13 | Flask          |
|           4 | Python        |           14 | Tornado        |
|           5 | *****         |           15 | *****          |
|           5 | *****         |           16 | *****          |
|           5 | *****         |           17 | *****          |
+-------------+---------------+--------------+----------------+
12 rows in set (0.00 sec)

mysql> select * from languages;
+------+--------+
| id   | name   |
+------+--------+
|    2 | Java   |
|    3 | Ruby   |
|    4 | Python |
|    5 | *****  |
+------+--------+
4 rows in set (0.00 sec)

mysql> select * from frameworks;
+------+-------------+---------------+
| id   | language_id | name          |
+------+-------------+---------------+
|    6 |           2 | -----         |
|    7 |           2 | -----         |
|    8 |           2 | -----         |
|    9 |           3 | Ruby on Rails |
|   10 |           3 | Sinatra       |
|   11 |           3 | Padrino       |
|   12 |           4 | Django        |
|   13 |           4 | Flask         |
|   14 |           4 | Tornado       |
|   15 |           5 | *****         |
|   16 |           5 | *****         |
|   17 |           5 | *****         |
+------+-------------+---------------+
12 rows in set (0.00 sec)

Pythonのframeworkをdeleteする場合

mysql> delete
    ->   f
    -> from
    ->   languages l
    ->   inner join frameworks f
    ->   on l.id = f.language_id
    -> where
    ->   l.name = 'Python';
Query OK, 3 rows affected (0.00 sec)

mysql> select
    ->   l.id language_id
    ->   , l.name language_name
    ->   , f.id framework_id
    ->   , f.name framework_name
    -> from
    ->  languages l
    ->  inner join frameworks f
    ->    on l.id = f.language_id;
+-------------+---------------+--------------+----------------+
| language_id | language_name | framework_id | framework_name |
+-------------+---------------+--------------+----------------+
|           2 | Java          |            6 | -----          |
|           2 | Java          |            7 | -----          |
|           2 | Java          |            8 | -----          |
|           3 | Ruby          |            9 | Ruby on Rails  |
|           3 | Ruby          |           10 | Sinatra        |
|           3 | Ruby          |           11 | Padrino        |
|           5 | *****         |           15 | *****          |
|           5 | *****         |           16 | *****          |
|           5 | *****         |           17 | *****          |
+-------------+---------------+--------------+----------------+
9 rows in set (0.00 sec)

mysql> select * from languages;
+------+--------+
| id   | name   |
+------+--------+
|    2 | Java   |
|    3 | Ruby   |
|    4 | Python |
|    5 | *****  |
+------+--------+
4 rows in set (0.00 sec)

mysql> select * from frameworks;
+------+-------------+---------------+
| id   | language_id | name          |
+------+-------------+---------------+
|    6 |           2 | -----         |
|    7 |           2 | -----         |
|    8 |           2 | -----         |
|    9 |           3 | Ruby on Rails |
|   10 |           3 | Sinatra       |
|   11 |           3 | Padrino       |
|   15 |           5 | *****         |
|   16 |           5 | *****         |
|   17 |           5 | *****         |
+------+-------------+---------------+
9 rows in set (0.00 sec)

参考