アップデート（UPDATE）方法
SQLでテーブル内のデータをアップデートするには「UPDATE関数」を使います。
UPDATE関数を使えば、すべてのデータを一括更新できたり、条件式を満たすデータのみをアップデート可能です。
UPDATE関数の基本的な使い方は次のようになります。
UPDATE テーブル名 SET 列名 = 値, 列名 = 値 ...;
では、サンプルを通してアップデートする方法を確認していきましょう。
ここでは次のテーブルを用いて説明します。
mydb=# SELECT * FROM up_table; id | name | score ----+---------+------- 1 | Sasaki | 70 2 | Tanaka | 84 3 | Akaishi | 66 4 | Minami | 90 5 | Kimura | 87 (5 rows)
では、scoreカラムのデータをすべて「0」にアップデートしてみます。
scoreカラムのデータをアップデートするには、次のように記述しましょう。
UPDATE up_table SET score = 0;
上記SQLクエリを実行すると、次のようにすべてのデータのscoreカラムの値が0になっているはずです。
mydb=# SELECT * FROM up_table; id | name | score ----+---------+------- 1 | Sasaki | 0 2 | Tanaka | 0 3 | Akaishi | 0 4 | Minami | 0 5 | Kimura | 0 (5 rows)
WHERE句で条件を指定する
条件付きでアップデートしたい場合は、SETの後ろにWHERE句を追加しましょう。
UPDATE テーブル名 SET 列名 = 値, 列名 = 値 … WHERE 条件;
ここでは、先ほど0にしたscoreカラムのデータを復元する形でアップデートしてみます。
UPDATE up_table SET score = 70 WHERE id = 1;
上記SQLクエリは、idカラムの値が「1」であるデータのscoreカラムの値を「70」にアップデートする記述です。
実行してテーブルを確認してみると、idが1のデータのscoreが70になっています。
mydb=# SELECT * FROM up_table; id | name | score ----+---------+------- 2 | Tanaka | 0 3 | Akaishi | 0 4 | Minami | 0 5 | Kimura | 0 1 | Sasaki | 70 (5 rows)
CASE文で条件分岐を行う
アップデートをCASE文で条件分岐して行うことも可能です。
CASE文の基本的な書き方は次の通りです。
CASE
WHEN [条件A] THEN [ルートA]
WHEN [条件A] THEN [ルートA]
・
・
・
ELSE [どの条件にも一致しない場合のルート]
END
では、アップデート文と組み合わせて条件分岐してみましょう。
UPDATE up_table SET name = CASE WHEN id > 3 then 'Hashimoto' ELSE 'Mizuno' END WHERE id IN(1, 2, 3, 4, 5);
上記のアップデート文は、idカラムが3より大きい場合はnameカラムの値を「Hashimoto」に、そうでない場合は「Mizuno」に更新するという意味です。
WHERE句内の「IN」で、すべてのidにアップデートをかけるように条件指定をしています。
実行すると、次のようにアップデートされているはずです。
mydb=# SELECT * FROM up_table; id | name | score ----+-----------+------- 1 | Mizuno | 70 2 | Mizuno | 84 3 | Mizuno | 66 4 | Hashimoto | 90 5 | Hashimoto | 87 (5 rows)
CASE文の使い方については、以下の記事でも解説していますので参考までに。
【関連記事】
▶︎【SQL】複雑な条件にも対応できるCASE式について解説。
計算式を使うことも可能
アップデート時に計算した値を使って更新も可能です。
ここでは、scoreの値を計算して更新してみましょう。
up_tableテーブルの情報は初期の状態に戻しておきました。
mydb=# SELECT * FROM up_table; id | name | score ----+---------+------- 1 | Sasaki | 70 2 | Tanaka | 84 3 | Akaishi | 66 4 | Minami | 90 5 | Kimura | 87 (5 rows)
試しに、「Sasaki」さんのscoreの値を倍にしてみます。
次のように記述しましょう。
UPDATE up_table SET score = score * 2 WHERE id = 1; mydb=# SELECT * FROM up_table; id | name | score ----+---------+------- 2 | Tanaka | 84 3 | Akaishi | 66 4 | Minami | 90 5 | Kimura | 87 1 | Sasaki | 140 (5 rows)
実行結果を見ると、scoreが70の2倍である「140」にアップデートされています。
このように、計算式をアップデート文に含めることも可能です。
JOINでテーブル連結してアップデート
JOINは、テーブル同士を連結させたい場合に使うSQLクエリです。
アップデートではこのJOINを組み合わせることで、テーブルを連結してデータ更新できます。
サンプルでテーブル連結のアップデートをみていきましょう。
PostgreSQLではJOINではなく別の方法でアップデートするので、ここではMySQLで説明します。
下記のサンプルテーブルを追加で用意しました。
mysql> select * from score_table; +------+-------+ | id | score | +------+-------+ | 1 | 10 | | 2 | 10 | | 3 | 10 | | 4 | 10 | | 5 | 10 | +------+-------+ 5 rows in set (0.00 sec)
この「score_table」と「up_table」の「id」で連結してアップデートしてみます。
mysql> UPDATE up_table INNER JOIN score_table ON up_table.id = score_table.id SET up_table.score = score_table.score; Query OK, 5 rows affected (0.01 sec) Rows matched: 5 Changed: 5 Warnings: 0 mysql> select * from up_table; +------+---------+-------+ | id | name | score | +------+---------+-------+ | 1 | Sasaki | 10 | | 2 | Tanaka | 10 | | 3 | Akaishi | 10 | | 4 | Minami | 10 | | 5 | Kimura | 10 | +------+---------+-------+ 5 rows in set (0.00 sec)
このように、scoreテーブルの値をup_tableテーブルに上書きできています。
なお、JOINの使い方については以下の記事でも解説しています。
【関連記事】
▶︎SQLで複数テーブルを扱うにはJOINで結合 内部結合と外部結合の使い分け
PostgreSQLではJOINを使わずに、次のようにWHERE句でテーブルの値を連結させます。
UPDATE up_table SET score = score_table.score FROM score_table WHERE up_table.id = score_table.id; mydb=# select * from up_table; id | name | score ----+---------+------- 1 | Sasaki | 10 2 | Tanaka | 10 3 | Akaishi | 10 4 | Minami | 10 5 | Kimura | 10 (5 rows)
LIMIT句で更新データを制限
LIMIT句を使えば、アップデートしたいデータの数を制限することも可能です！
こちらもサンプルを使いながら確認してみましょう。
PostgreSQLは少し使い方が特殊なので、JOIN同様、MySQLからやり方を見ていきます。
UPDATE up_table SET score = 0 LIMIT 2; mysql> SELECT * FROM up_table; +------+---------+-------+ | id | name | score | +------+---------+-------+ | 1 | Sasaki | 0 | | 2 | Tanaka | 0 | | 3 | Akaishi | 10 | | 4 | Minami | 10 | | 5 | Kimura | 10 | +------+---------+-------+ 5 rows in set (0.00 sec)
上記のようにLIMIT句を使ってアップデートを2件までに制限できました。
また、WHERE句やORDER BY句と組み合わせることで、更新箇所を指定しつつ更新数を制限することも可能です。
UPDATE up_table SET score = 50 ORDER BY score DESC LIMIT 3; mysql> SELECT * FROM up_table; | id | name | score | +------+---------+-------+ | 1 | Sasaki | 0 | | 2 | Tanaka | 0 | | 3 | Akaishi | 50 | | 4 | Minami | 50 | | 5 | Kimura | 50 | +------+---------+-------+ 5 rows in set (0.00 sec)
上記は、scoreの値が大きい順から3つまでのデータを更新してみました！
PostgreSQLでLIMITを使うには、次のように記述します。
UPDATE up_table SET score = 0 WHERE id IN(SELECT id FROM up_table WHERE id > 0 ORDER BY id LIMIT 2); mydb=# SELECT * FROM up_table; id | name | score ----+---------+------- 3 | Akaishi | 10 4 | Minami | 10 5 | Kimura | 10 1 | Sasaki | 0 2 | Tanaka | 0 (5 rows)
WHERE句の後にINを使ってアップデートを制限しています。
また、ORDER BY句を使ってid順に並び替えました。
MySQLと比べると少し複雑になりますが、LIMITで制限しながら更新可能です。
複数行・複数列のアップデート
テーブル内のデータを複数業アップデートしたい場合は、「CASE文で条件分岐を行う」で紹介したCASE文を使えばOKです。
複数列をアップデートしたい場合は、SETの後ろに更新するカラム名と値を追加しましょう。
サンプルでidが3のデータのnameとscoreの値を更新してみます。
UPDATE up_table SET name = 'Ebina', score = 100 WHERE id =3; mydb=# SELECT * FROM up_table; id | name | score ----+--------+------- 4 | Minami | 10 5 | Kimura | 10 1 | Sasaki | 0 2 | Tanaka | 0 3 | Ebina | 100 (5 rows)
このように、複数列のアップデートは比較的簡単にできました。
複数テーブルのアップデート
複数テーブルをアップデートしたい場合は、更新したいテーブルごとにアップデートする必要があります。
つまり、1度に2つ、3つといったテーブルの更新はできません。
日付型のアップデート
日付型のデータをアップデートする場合は、「TO_DATE関数」を使いましょう。
アップデート方法を確認するために、次のテーブルを新規で用意しました。
CREATE TABLE up2_table(id integer, name varchar(20), date timestamp not null default now());
mydb=# \d up2_table;
Table "public.up2_table"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
id | integer | | |
name | character varying(20) | | |
date | timestamp without time zone | | not null | now()
上記のようにdateカラムをtimestamp(日付型)にし、現在の日時がデータ追加時に自動で入るように作成しました。
このテーブルに次のようにデータを追加します。
mydb=# select * from up2_table; id | name | date ----+---------+---------------------------- 1 | Tanaka | 2020-08-23 15:26:38.329389 2 | Kikuchi | 2020-08-23 15:31:18.611994 3 | Taguchi | 2020-08-23 15:31:27.784123 (3 rows)
この状態で、dateカラムの値をアップデートしてみます。
mydb=# SELECT * FROM up2_table; id | name | date ----+---------+--------------------- 1 | Tanaka | 2020-01-01 00:00:00 2 | Kikuchi | 2020-01-01 00:00:00 3 | Taguchi | 2020-01-01 00:00:00 (3 rows)
このように、dateカラムの値が更新されました。
今回は「’yyyy-mm-dd」書式で更新したため、時間部分は0になっています。
TO_DATE関数の使い方について、以下の記事にまとめていますので知りたい方は参考にしてください。
【関連記事】
▶︎【SQL】TO_DATE関数の使い方｜PostgreSQL・MySQLでの代用法も
値をNULLにアップデート
アップデート時に値を「NULL」にすることもできます。
次のように、NULLにしたいカラムを指定して更新すればOKです。
UPDATE up_table SET name = null WHERE id = 4; mydb=# SELECT * FROM up_table; id | name | score ----+--------+------- 5 | Kimura | 0 1 | Sasaki | 0 2 | Tanaka | 0 3 | Ebina | 0 4 | | 0 (5 rows)
上記のように、idが4のnameの値がNULLになりました。
ただし、カラムに「NOT NULL」を設定している場合は、NULLでアップデートできません。
mydb=# UPDATE up2_table SET date = null; ERROR: null value in column "date" violates not-null constraint DETAIL: Failing row contains (1, Tanaka, null).
このように、アップデートしようと実行した際にエラーが出ます。
まとめ
SQLでテーブルのデータをアップデートする方法について解説・まとめました。
条件を組み合わせることで複雑なアップデートも可能です。
ぜひ、この記事を参考にアップデートする方法をマスターしてください！
