はじめに
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)