SQL 第2版 ゼロからはじめるデータベース操作 アウトプット（第6回）

## 概要
- 本記事は[SQL 第2版 ゼロからはじめるデータベース操作](https://www.shoeisha.co.jp/book/detail/9784798144450)で学んだ内容をまとめたものです。
  - 前回は「**第3章：集約と並び替え**」3-3: 集約した結果に条件を指定する, 3-4: 検索結果を並び替える で学んだ内容をまとめました：[SQL 第2版 ゼロからはじめるデータベース操作 アウトプット（第5回）](https://qiita.com/takumi_links/items/95bb93f7d8d2b0ceba3e)
  - 今回は「**第4章：データの更新**」4-1: データの登録, 4-2: データの削除, 4-3: データの更新 についてまとめていきます
  - 「**第4章：データの更新**」4-4: トランザクション については次回まとめます

### データの登録
- テーブルにデータ（行）を登録するには、`INSERT`文を使う
  - 原則として、`INSERT`文は1回の実行で1行のデータを登録する
- 列名や値をカンマで区切って、外側をかっこ()で括った形式を「**リスト**」と呼ぶ
  - 列リストと値リストが存在する。**列リストと値リストの列数は一致している必要がある**
  - 列リスト：登録する列名のリスト `(列名1, 列名2, ...)`
  - 値リスト：登録するデータのリスト `(値1, 値2, ...)`
- テーブル名の後の列リストは、テーブルの全列に対してデータを登録する場合は省略できる
- `INSERT`文の基本構文
  - `INSERT INTO テーブル名 (列名1, 列名2, ...) VALUES (値1, 値2, ...);`
- 値リストの数が不足してたりするとエラーになる

```sql
mysql> CREATE TABLE ShohinIns
  -> (shohin_id CHAR(4) NOT NULL,
       ->  shohin_mei VARCHAR(100) NOT NULL,
       ->  shohin_bunrui VARCHAR(32) NOT NULL,
       ->  hanbai_tanka INTEGER DEFAULT 0,
       ->  shiire_tanka INTEGER ,
       ->  torokubi DATE ,
       ->      PRIMARY KEY (shohin_id));
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT * FROM ShohinIns;
Empty set (0.02 sec)

mysql> INSERT INTO ShohinIns (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi) VALUES ('0001', 'T-Shirt', 'Clothing', 1000, 500, '2009-09-20');
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM ShohinIns;
+-----------+------------+---------------+--------------+--------------+------------+
| shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+------------+---------------+--------------+--------------+------------+
| 0001      | T-Shirt    | Clothing      |         1000 |          500 | 2009-09-20 |
+-----------+------------+---------------+--------------+--------------+------------+
1 row in set (0.00 sec)
             
mysql> INSERT INTO ShohinIns (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi) VALUES ('0001', 'T-Shirt', 'Clothing', 1000, 500);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
```

#### 複数行の登録
- 複数行のデータを登録する場合は、`INSERT`文を複数回実行するか、`INSERT`文の値リストをカンマで区切って記述する
  - `INSERT`文の値リストをカンマで区切って記述する場合は、`VALUES`句の後にカンマで区切って値リストを記述する
  - `INSERT`文の記述内容に間違いがあると、エラーが発生する = どの行のどの箇所でエラーだったのかが特定しづらい

```sql
mysql> INSERT INTO ShohinIns (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi) VALUES ('0002', 'Hole Punch', 'Office Supplies', 500, 320, '2009-09-11'), ('0003', 'Dress Shirt', 'Clothing', 4000, 2800, NULL), ('0004', 'Kitchen Knife', 'Kitchen Supplies', 3000, 2800, '2009-09-20') ;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM ShohinIns;
+-----------+---------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei    | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+---------------+------------------+--------------+--------------+------------+
| 0001      | T-Shirt       | Clothing         |         1000 |          500 | 2009-09-20 |
| 0002      | Hole Punch    | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0003      | Dress Shirt   | Clothing         |         4000 |         2800 | NULL       |
| 0004      | Kitchen Knife | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
+-----------+---------------+------------------+--------------+--------------+------------+
4 rows in set (0.00 sec)
```

#### INSERTにおけるポイント
- 列リストの省略
  - テーブルの全列に対してデータを登録する場合は、列リストを省略できる
```sql
mysql> INSERT INTO ShohinIns VALUES ('0005', 'Pressure Cooker', 'Kitchen Supplies', 6800, 5000, '2009-01-15');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM ShohinIns;
+-----------+-----------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei      | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+-----------------+------------------+--------------+--------------+------------+
| 0001      | T-Shirt         | Clothing         |         1000 |          500 | 2009-09-20 |
| 0002      | Hole Punch      | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0003      | Dress Shirt     | Clothing         |         4000 |         2800 | NULL       |
| 0004      | Kitchen Knife   | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
| 0005      | Pressure Cooker | Kitchen Supplies |         6800 |         5000 | 2009-01-15 |
+-----------+-----------------+------------------+--------------+--------------+------------+
5 rows in set (0.00 sec)
```
- `NULL`を挿入する場合
  - `NULL`を挿入する場合は、値リストにそのまま`NULL`を設定する

```sql
mysql> INSERT INTO ShohinIns VALUES ('0006', 'Fork', 'Kitchen Supplies', 500, NULL, '2009-09-20');
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM ShohinIns;
+-----------+-----------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei      | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+-----------------+------------------+--------------+--------------+------------+
| 0001      | T-Shirt         | Clothing         |         1000 |          500 | 2009-09-20 |
| 0002      | Hole Punch      | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0003      | Dress Shirt     | Clothing         |         4000 |         2800 | NULL       |
| 0004      | Kitchen Knife   | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
| 0005      | Pressure Cooker | Kitchen Supplies |         6800 |         5000 | 2009-01-15 |
| 0006      | Fork            | Kitchen Supplies |          500 |         NULL | 2009-09-20 |
+-----------+-----------------+------------------+--------------+--------------+------------+
6 rows in set (0.00 sec)
```
- デフォルト値を挿入する
  - デフォルト値を挿入する場合は、値リストにそのまま`DEFAULT`を設定する
  - もしくは、暗黙的にデフォルト値を挿入する場合は、列リストを省略する
- デフォルト値が設定されていない列を省略すると`NULL`が挿入される
  - `NOT NULL`制約が設定されている列に`NULL`を挿入するとエラーになる

```sql
mysql> INSERT INTO ShohinIns (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi) VALUES ('0007', 'Grater', 'Kitchen Supplies', DEFAULT, 790, '2009-04-28');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM ShohinIns;
+-----------+-----------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei      | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+-----------------+------------------+--------------+--------------+------------+
| 0001      | T-Shirt         | Clothing         |         1000 |          500 | 2009-09-20 |
| 0002      | Hole Punch      | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0003      | Dress Shirt     | Clothing         |         4000 |         2800 | NULL       |
| 0004      | Kitchen Knife   | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
| 0005      | Pressure Cooker | Kitchen Supplies |         6800 |         5000 | 2009-01-15 |
| 0006      | Fork            | Kitchen Supplies |          500 |         NULL | 2009-09-20 |
| 0007      | Grater          | Kitchen Supplies |            0 |          790 | 2009-04-28 |
+-----------+-----------------+------------------+--------------+--------------+------------+
7 rows in set (0.00 sec)

-- 列リストを省略する場合
mysql> INSERT INTO ShohinIns (shohin_id, shohin_mei, shohin_bunrui, shiire_tanka, torokubi) VALUES ('0007', 'Grater', 'Kitchen Supplies', 790, '2009-04-28');
Query OK, 1 row affected (0.02 sec)


mysql> SELECT * FROM ShohinIns;
+-----------+-----------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei      | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+-----------------+------------------+--------------+--------------+------------+
| 0001      | T-Shirt         | Clothing         |         1000 |          500 | 2009-09-20 |
| 0002      | Hole Punch      | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0003      | Dress Shirt     | Clothing         |         4000 |         2800 | NULL       |
| 0004      | Kitchen Knife   | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
| 0005      | Pressure Cooker | Kitchen Supplies |         6800 |         5000 | 2009-01-15 |
| 0006      | Fork            | Kitchen Supplies |          500 |         NULL | 2009-09-20 |
| 0007      | Grater          | Kitchen Supplies |            0 |          790 | 2009-04-28 |
+-----------+-----------------+------------------+--------------+--------------+------------+
7 rows in set (0.00 sec)

-- shiire_tanka列を省略するとNULLが挿入される
mysql> INSERT INTO ShohinIns (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, torokubi) VALUES ('0008', 'Ballpoint pen', 'Office Supplies', 100, '2009-04-28');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM ShohinIns;
+-----------+-----------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei      | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+-----------------+------------------+--------------+--------------+------------+
| 0001      | T-Shirt         | Clothing         |         1000 |          500 | 2009-09-20 |
| 0002      | Hole Punch      | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0003      | Dress Shirt     | Clothing         |         4000 |         2800 | NULL       |
| 0004      | Kitchen Knife   | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
| 0005      | Pressure Cooker | Kitchen Supplies |         6800 |         5000 | 2009-01-15 |
| 0006      | Fork            | Kitchen Supplies |          500 |         NULL | 2009-09-20 |
| 0007      | Grater          | Kitchen Supplies |            0 |          790 | 2009-04-28 |
| 0008      | Ballpoint pen   | Office Supplies  |          100 |         NULL | 2009-04-28 |
+-----------+-----------------+------------------+--------------+--------------+------------+
8 rows in set (0.00 sec)

-- shohin_mei列を省略するとエラーになる：NOT NULL制約に違反
mysql> INSERT INTO ShohinIns (shohin_id, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi) VALUES ('0009', 'Office Supplies', 1000, 500, '2009-12-12');
ERROR 1364 (HY000): Field 'shohin_mei' doesn't have a default value
```

#### 他のテーブルからデータをコピーする
- データを挿入する方法として、**他のテーブルからデータをコピーする**方法がある
  - `INSERT INTO`句に`SELECT`句を指定することで、他のテーブルからデータをコピーできる
  - `SELECT`句で指定する列数とデータ型は、`INSERT INTO`句で指定する列数とデータ型と一致している必要がある
  - `SELECT`句で指定する列名は、`INSERT INTO`句で指定する列名と一致している必要はない
  - `SELECT`句で指定する列名は、`INSERT INTO`句で指定する列名と一致している場合は省略できる
- `Shohin`テーブルと同じ構造のテーブル：`ShohinCopy`を作成し、`Shohin`テーブルのデータをコピーすれば、データのバックアップが可能

```sql
mysql> CREATE TABLE ShohinCopy
  -> (
       ->     shohin_id     CHAR(4)      NOT NULL,
       ->     shohin_mei    VARCHAR(100) NOT NULL,
       ->     shohin_bunrui VARCHAR(32)  NOT NULL,
       ->     hanbai_tanka  INTEGER,
       ->     shiire_tanka  INTEGER,
       ->     torokubi      DATE,
       ->     PRIMARY KEY (shohin_id)
       -> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO ShohinCopy (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi) SELECT shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi FROM Shohin;
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM ShohinCopy;
+-----------+-----------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei      | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+-----------------+------------------+--------------+--------------+------------+
| 0001      | T-shirt         | Clothing         |         1000 |          500 | 2009-09-20 |
| 0002      | Hole Punch      | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0003      | Dress Shirt     | Clothing         |         4000 |         2800 | NULL       |
| 0004      | Kitchen Knife   | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
| 0005      | Pressure Cooker | Kitchen Supplies |         6800 |         5000 | 2009-01-15 |
| 0006      | Fork            | Kitchen Supplies |          500 |         NULL | 2009-09-20 |
| 0007      | Grater          | Kitchen Supplies |          880 |          790 | 2008-04-28 |
| 0008      | Ballpoint Pen   | Office Supplies  |          100 |         NULL | 2009-11-11 |
+-----------+-----------------+------------------+--------------+--------------+------------+
8 rows in set (0.00 sec)

mysql> INSERT INTO ShohinCopy (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi) SELECT * FROM Shohin;
Query OK, 8 rows affected (0.02 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM ShohinCopy;
+-----------+-----------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei      | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+-----------------+------------------+--------------+--------------+------------+
| 0001      | T-shirt         | Clothing         |         1000 |          500 | 2009-09-20 |
| 0002      | Hole Punch      | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0003      | Dress Shirt     | Clothing         |         4000 |         2800 | NULL       |
| 0004      | Kitchen Knife   | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
| 0005      | Pressure Cooker | Kitchen Supplies |         6800 |         5000 | 2009-01-15 |
| 0006      | Fork            | Kitchen Supplies |          500 |         NULL | 2009-09-20 |
| 0007      | Grater          | Kitchen Supplies |          880 |          790 | 2008-04-28 |
| 0008      | Ballpoint Pen   | Office Supplies  |          100 |         NULL | 2009-11-11 |
+-----------+-----------------+------------------+--------------+--------------+------------+
8 rows in set (0.00 sec)

mysql> DELETE FROM ShohinCopy;
Query OK, 8 rows affected (0.00 sec)

mysql> INSERT INTO ShohinCopy SELECT * FROM Shohin;
Query OK, 8 rows affected (0.02 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM ShohinCopy;
+-----------+-----------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei      | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+-----------------+------------------+--------------+--------------+------------+
| 0001      | T-shirt         | Clothing         |         1000 |          500 | 2009-09-20 |
| 0002      | Hole Punch      | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0003      | Dress Shirt     | Clothing         |         4000 |         2800 | NULL       |
| 0004      | Kitchen Knife   | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
| 0005      | Pressure Cooker | Kitchen Supplies |         6800 |         5000 | 2009-01-15 |
| 0006      | Fork            | Kitchen Supplies |          500 |         NULL | 2009-09-20 |
| 0007      | Grater          | Kitchen Supplies |          880 |          790 | 2008-04-28 |
| 0008      | Ballpoint Pen   | Office Supplies  |          100 |         NULL | 2009-11-11 |
+-----------+-----------------+------------------+--------------+--------------+------------+
8 rows in set (0.00 sec)
```
- `INSERT`文中の`SELECT`文には、`WHERE`句や`ORDER BY`句を指定することもできる
  - `WHERE`句や`ORDER BY`句を指定することで、特定のデータだけをコピーすることも可能

```sql
mysql> CREATE TABLE ShohinBunrui
  -> (
       ->     shohin_bunrui    VARCHAR(32) NOT NULL,
       ->     sum_hanbai_tanka INTEGER,
       ->     sum_shiire_tanka INTEGER,
       ->     PRIMARY KEY (shohin_bunrui)
       -> );
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO ShohinBunrui (shohin_bunrui, sum_hanbai_tanka, sum_shiire_tanka) SELECT shohin_bunrui, SUM(hanbai_tanka), SUM(shiire_tanka) FROM Shohin GROUP BY shohin_bunrui;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM ShohinBunrui;
+------------------+------------------+------------------+
| shohin_bunrui    | sum_hanbai_tanka | sum_shiire_tanka |
+------------------+------------------+------------------+
| Clothing         |             5000 |             3300 |
| Kitchen Supplies |            11180 |             8590 |
| Office Supplies  |              600 |              320 |
+------------------+------------------+------------------+
3 rows in set (0.00 sec)
```

### データの削除
- テーブルからデータ（行）を削除するには、`DELETE`文を使う
  - `DELETE`文は、`WHERE`句を使って削除対象のデータを指定する
  - `DELETE`文を実行すると、削除対象のデータが削除される
  - `WHERE`句をつけない場合、テーブルの全データが削除される

```sql
mysql> SELECT * FROM ShohinCopy;
+-----------+-----------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei      | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+-----------------+------------------+--------------+--------------+------------+
| 0001      | T-shirt         | Clothing         |         1000 |          500 | 2009-09-20 |
| 0002      | Hole Punch      | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0003      | Dress Shirt     | Clothing         |         4000 |         2800 | NULL       |
| 0004      | Kitchen Knife   | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
| 0005      | Pressure Cooker | Kitchen Supplies |         6800 |         5000 | 2009-01-15 |
| 0006      | Fork            | Kitchen Supplies |          500 |         NULL | 2009-09-20 |
| 0007      | Grater          | Kitchen Supplies |          880 |          790 | 2008-04-28 |
| 0008      | Ballpoint Pen   | Office Supplies  |          100 |         NULL | 2009-11-11 |
+-----------+-----------------+------------------+--------------+--------------+------------+
8 rows in set (0.00 sec)

mysql> DELETE FROM ShohinCopy WHERE hanbai_tanka >= 4000;
Query OK, 2 rows affected (0.01 sec)

mysql> SELECT * FROM ShohinCopy;
+-----------+---------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei    | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+---------------+------------------+--------------+--------------+------------+
| 0001      | T-shirt       | Clothing         |         1000 |          500 | 2009-09-20 |
| 0002      | Hole Punch    | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0004      | Kitchen Knife | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
| 0006      | Fork          | Kitchen Supplies |          500 |         NULL | 2009-09-20 |
| 0007      | Grater        | Kitchen Supplies |          880 |          790 | 2008-04-28 |
| 0008      | Ballpoint Pen | Office Supplies  |          100 |         NULL | 2009-11-11 |
+-----------+---------------+------------------+--------------+--------------+------------+
6 rows in set (0.01 sec)
```

- `DELETE`文以外にも、多くのDBMSでは`TRUNCATE`文を使ってテーブルの全データを削除することができる
  - `TRUNCATE`文は、**テーブルの全データを削除する**ため、`WHERE`句を指定することはできない

```sql
mysql> SELECT * FROM ShohinCopy;
+-----------+---------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei    | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+---------------+------------------+--------------+--------------+------------+
| 0001      | T-shirt       | Clothing         |         1000 |          500 | 2009-09-20 |
| 0002      | Hole Punch    | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0004      | Kitchen Knife | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
| 0006      | Fork          | Kitchen Supplies |          500 |         NULL | 2009-09-20 |
| 0007      | Grater        | Kitchen Supplies |          880 |          790 | 2008-04-28 |
| 0008      | Ballpoint Pen | Office Supplies  |          100 |         NULL | 2009-11-11 |
+-----------+---------------+------------------+--------------+--------------+------------+
6 rows in set (0.00 sec)

mysql> TRUNCATE ShohinCopy;
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT * FROM ShohinCopy;
Empty set (0.00 sec)
```

### データの更新
- テーブルのデータ（行）を更新するには、`UPDATE`文を使う
  - `UPDATE`文は、`SET`句を使って更新する列と値を指定し、`WHERE`句を使って更新対象のデータを指定する
  - `UPDATE`文を実行すると、更新対象のデータが更新される
  - `WHERE`句をつけない場合、テーブルの全データが更新される

```sql
mysql> SELECT * FROM Shohin;
+-----------+---------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei    | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+---------------+------------------+--------------+--------------+------------+
| 0001      | T-shirt       | Clothing         |         1000 |          500 | 2009-09-20 |
| 0002      | Hole Punch    | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0004      | Kitchen Knife | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
| 0006      | Fork          | Kitchen Supplies |          500 |         NULL | 2009-09-20 |
| 0007      | Grater        | Kitchen Supplies |          880 |          790 | 2008-04-28 |
| 0008      | Ballpoint Pen | Office Supplies  |          100 |         NULL | 2009-11-11 |
+-----------+---------------+------------------+--------------+--------------+------------+
6 rows in set (0.00 sec)

mysql> UPDATE Shohin SET torokubi = '2009-10-10';
Query OK, 6 rows affected (0.02 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> SELECT * FROM Shohin;
+-----------+---------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei    | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+---------------+------------------+--------------+--------------+------------+
| 0001      | T-shirt       | Clothing         |         1000 |          500 | 2009-10-10 |
| 0002      | Hole Punch    | Office Supplies  |          500 |          320 | 2009-10-10 |
| 0004      | Kitchen Knife | Kitchen Supplies |         3000 |         2800 | 2009-10-10 |
| 0006      | Fork          | Kitchen Supplies |          500 |         NULL | 2009-10-10 |
| 0007      | Grater        | Kitchen Supplies |          880 |          790 | 2009-10-10 |
| 0008      | Ballpoint Pen | Office Supplies  |          100 |         NULL | 2009-10-10 |
+-----------+---------------+------------------+--------------+--------------+------------+
6 rows in set (0.00 sec)

mysql> UPDATE Shohin SET hanbai_tanka = hanbai_tanka * 100 WHERE shohin_bunrui = 'Kitchen Supplies';
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM Shohin;
+-----------+---------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei    | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+---------------+------------------+--------------+--------------+------------+
| 0001      | T-shirt       | Clothing         |         1000 |          500 | 2009-10-10 |
| 0002      | Hole Punch    | Office Supplies  |          500 |          320 | 2009-10-10 |
| 0004      | Kitchen Knife | Kitchen Supplies |       300000 |         2800 | 2009-10-10 |
| 0006      | Fork          | Kitchen Supplies |        50000 |         NULL | 2009-10-10 |
| 0007      | Grater        | Kitchen Supplies |        88000 |          790 | 2009-10-10 |
| 0008      | Ballpoint Pen | Office Supplies  |          100 |         NULL | 2009-10-10 |
+-----------+---------------+------------------+--------------+--------------+------------+
6 rows in set (0.01 sec)
```
- 値をNULLで更新することを「**NULLクリア**」と呼ぶ
  - NULLクリアが可能なのは、NULLを許容する列のみ（= NOT NULL制約が設定されていない列）
```sql
mysql> UPDATE Shohin SET torokubi = NULL WHERE shohin_id = 0008;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM Shohin;
+-----------+---------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei    | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+---------------+------------------+--------------+--------------+------------+
| 0001      | T-shirt       | Clothing         |         1000 |          500 | 2009-10-10 |
| 0002      | Hole Punch    | Office Supplies  |          500 |          320 | 2009-10-10 |
| 0004      | Kitchen Knife | Kitchen Supplies |       300000 |         2800 | 2009-10-10 |
| 0006      | Fork          | Kitchen Supplies |        50000 |         NULL | 2009-10-10 |
| 0007      | Grater        | Kitchen Supplies |        88000 |          790 | 2009-10-10 |
| 0008      | Ballpoint Pen | Office Supplies  |          100 |         NULL | NULL       |
+-----------+---------------+------------------+--------------+--------------+------------+
6 rows in set (0.00 sec)

mysql> UPDATE Shohin SET shohin_mei = NULL WHERE shohin_id = 0008;
ERROR 1048 (23000): Column 'shohin_mei' cannot be null
```
- データの更新は複数列に対して行うことも可能
  - `SET`句に複数の列と値を指定することで、複数列に対して一度に更新できる
```sql
mysql> UPDATE Shohin SET hanbai_tanka = hanbai_tanka * 10, shiire_tanka = shiire_tanka / 2  WHERE shohin_bunrui = 'Kitchen Supplies';
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM Shohin;
+-----------+---------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei    | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+---------------+------------------+--------------+--------------+------------+
| 0001      | T-shirt       | Clothing         |         1000 |          500 | 2009-10-10 |
| 0002      | Hole Punch    | Office Supplies  |          500 |          320 | 2009-10-10 |
| 0004      | Kitchen Knife | Kitchen Supplies |      3000000 |         1400 | 2009-10-10 |
| 0006      | Fork          | Kitchen Supplies |       500000 |         NULL | 2009-10-10 |
| 0007      | Grater        | Kitchen Supplies |       880000 |          395 | 2009-10-10 |
| 0008      | Ballpoint Pen | Office Supplies  |          100 |         NULL | NULL       |
+-----------+---------------+------------------+--------------+--------------+------------+
6 rows in set (0.00 sec)
```

### まとめ
- 本記事では「**第4章：データの更新**」4-1: データの登録, 4-2: データの削除, 4-3: データの更新 についてまとめました！

## 参考
- [SQL 第2版 ゼロからはじめるデータベース操作](https://www.shoeisha.co.jp/book/detail/9784798144450)