How To Update Data in SQL

序章

データベースを操作する場合、データベースにすでに挿入されているデータを変更する必要がある場合があります。 たとえば、スペルミスのあるエントリを修正する必要がある場合や、不完全なレコードに追加する新しい情報がある場合があります。 構造化照会言語—より一般的には SQL として知られています—は、ユーザーがテーブル内の既存のデータを変更できるようにするUPDATEキーワードを提供します。

このガイドでは、SQLのUPDATE構文を使用して1つ以上のテーブルのデータを変更する方法の概要を説明します。 また、SQLが外部キー制約と競合するUPDATE操作を処理する方法についても説明します。

前提条件

このガイドに従うには、SQLを使用するある種のリレーショナルデータベース管理システム（RDBMS）を実行しているコンピューターが必要です。 このガイドの手順と例は、次の環境を使用して検証されています。

• Ubuntu 20.04 の初期サーバーセットアップガイドで説明されているように、管理者権限を持つ非rootユーザーとUFWで構成されたファイアウォールを備えたUbuntu20.04を実行しているサーバー。
• Ubuntu 20.04にMySQLをインストールする方法で概説されているように、MySQLがサーバーにインストールされて保護されています。 このガイドは、ステップ3で説明されているプロセスを使用して作成された非ルートMySQLユーザーで検証されました。

• また、SQLデータの更新を練習するために使用できるサンプルデータがロードされたいくつかのテーブルを含むデータベースも必要です。 MySQLサーバーに接続し、このガイド全体の例で使用されるテストデータベースを作成する方法の詳細については、次のMySQLへの接続とサンプルデータベースのセットアップセクションを実行することをお勧めします。

このページに埋め込まれているインタラクティブ端末を使用して、このチュートリアルのサンプルクエリを試すこともできます。 次のLaunch an Interactive Terminal!ボタンをクリックして開始します。

インタラクティブターミナルを起動します！

MySQLへの接続とサンプルデータベースの設定

SQLデータベースシステムがリモートサーバーで実行されている場合は、ローカルマシンからサーバーにSSHで接続します。

ssh sammy@your_server_ip

次に、MySQLサーバープロンプトを開き、sammyをMySQLユーザーアカウントの名前に置き換えます。 このページに埋め込まれたインタラクティブ端末を使用している場合、プロンプトが表示されたときに使用するパスワードはsecretという単語であることに注意してください。

mysql -u sammy -p

updateDBという名前のデータベースを作成します。

CREATE DATABASE updateDB;

データベースが正常に作成されると、次のような出力が表示されます。

Output
Query OK, 1 row affected (0.01 sec)

updateDBデータベースを選択するには、次のUSEステートメントを実行します。

USE updateDB;

Output
Database changed

updateDBデータベースを選択した後、そのデータベース内にいくつかのテーブルを作成します。 このガイドで使用されている例では、タレントエージェンシーを運営していて、SQLデータベースでクライアントとそのパフォーマンスの追跡を開始することにしたと想像してください。 最初に2つのテーブルから始めることを計画しており、最初のテーブルにはクライアントに関する情報が格納されます。 このテーブルには4つの列が必要であると判断しました。

• clientID：intデータ型で表される各クライアントの識別番号。 この列はテーブルの主キーとしても機能し、各値はそれぞれの行の一意の識別子として機能します
• name：各クライアントの名前。最大20文字のvarcharデータ型を使用して表されます。
• routine：各クライアントの主要なパフォーマンスジャンルの簡単な説明。ここでもvarcharデータ型を使用して表現されますが、最大30文字です。
• performanceFee：各クライアントの標準成功報酬を記録する列。この列の値は最大5桁に制限され、そのうちの2桁はdecimalデータ型を使用します。小数点の右側。 したがって、この列で許可される値の範囲は、-999.99から999.99になります。

次の4つの列を持つclientsという名前のテーブルを作成します。

CREATE TABLE clients
(clientID int PRIMARY KEY,
name varchar(20),
routine varchar(30),
standardFee decimal (5,2)
);

2番目のテーブルには、ローカル会場でのクライアントのパフォーマンスに関する情報が格納されます。 このテーブルには5つの列が必要であると判断します。

• showID：clientID列と同様に、この列には、intデータ型で表される、各番組の一意の識別番号が保持されます。 同様に、この列はshowsテーブルの主キーとして機能します
• showDate：各公演の日付。 この列の値は、'YYYY-MM-DD'形式を使用するdateデータ型を使用して表されます。
• clientID：ショーで演奏しているクライアントのID番号。整数で表されます。
• attendance：整数で表された各公演の参加者数
• ticketPrice：各ショーでの個々のチケットの価格。 この列はdecimalデータ型を使用し、この列の値は最大4桁に制限され、そのうちの2桁は小数点の右側にあるため、この列で許可される値の範囲は次のとおりです。 -99.99から99.99

clientID列が有効なクライアントID番号を表す値のみを保持するようにするには、clientsテーブルのclientID列。 外部キー制約は、それが適用される列の値が、それが参照する列にすでに存在している必要があることを要求することによって、2つのテーブル間の関係を表現する方法です。 次の例では、FOREIGN KEY制約では、showsテーブルのclientID列に追加された値が、clientテーブルのclientID列。

次の5つの列を持つclientsという名前のテーブルを作成します。

CREATE TABLE shows
(showID int PRIMARY KEY,
showDate date,
clientID int,
attendance int,
ticketPrice decimal (4,2),
CONSTRAINT client_fk
FOREIGN KEY (clientID)
REFERENCES clients(clientID)
);

この例では、外部キー制約の名前client_fkが提供されていることに注意してください。 MySQLは、追加した制約の名前を自動的に生成しますが、後でこの制約を参照する必要がある場合は、ここで名前を定義すると便利です。

次に、次のINSERT INTOステートメントを実行して、clientsテーブルに5行のサンプルデータをロードします。

INSERT INTO clients
VALUES
(1, 'Gladys', 'song and dance', 180),
(2, 'Catherine', 'standup', 99.99),
(3, 'Georgeanna', 'standup', 45),
(4, 'Wanda', 'song and dance', 200),
(5, 'Ann', 'trained squirrel', 79.99);

次に、別のINSERT INTOステートメントを実行して、showsテーブルに10行のサンプルデータをロードします。

INSERT INTO shows
VALUES
(1, '2019-12-25', 4, 124, 15),
(2, '2020-01-11', 5, 84, 29.50),
(3, '2020-01-17', 3, 170, 12.99),
(4, '2020-01-31', 5, 234, 14.99),
(5, '2020-02-08', 1, 86, 25),
(6, '2020-02-14', 3, 102, 39.5),
(7, '2020-02-15', 2, 101, 26.50),
(8, '2020-02-27', 2, 186, 19.99),
(9, '2020-03-06', 4, 202, 30),
(10, '2020-03-07', 5, 250, 8.99);

これで、ガイドの残りの部分に従い、SQLを使用してデータを更新する方法について学習する準備が整いました。

単一のテーブルのデータを更新する

UPDATEステートメントの一般的な構文は次のようになります。

UPDATE table_name
SET column_name = value_expression
WHERE conditions_apply;

UPDATEキーワードの後には、更新するデータを格納するテーブルの名前が続きます。 その後、SET句があり、どの列のデータをどのように更新するかを指定します。 SET句は、指定された列の値を、指定した値式と同じに設定するものと考えてください。

SQLでは、値式（スカラー式とも呼ばれます）は、更新されるすべての行に対して単一の値を返す任意の式です。 これは、文字列リテラル、または列内の既存の数値に対して実行される数学演算である可能性があります。 すべてのUPDATEステートメントに少なくとも1つの値の割り当てを含める必要がありますが、複数の列のデータを更新するために複数の値の割り当てを含めることができます。

SET句の後には、WHERE句があります。 この構文例のようにUPDATEステートメントにWHERE句を含めると、更新したくない行を除外できます。 WHERE句は、UPDATEステートメントでは完全にオプションですが、これを含めない場合、操作はテーブルのすべての行を更新します。

SQLがUPDATE操作を処理する方法を説明するために、clientsテーブルのすべてのデータを確認することから始めます。 次のクエリには、テーブルのすべての列を表すSQLの省略形であるアスタリスク（*）が含まれているため、このクエリはclientsテーブルのすべての列からすべてのデータを返します。

SELECT * FROM clients;

Output
+----------+------------+------------------+-------------+
| clientID | name       | routine          | standardFee |
+----------+------------+------------------+-------------+
|        1 | Gladys     | song and dance   |      180.00 |
|        2 | Catherine  | standup          |       99.99 |
|        3 | Georgeanna | standup          |       45.00 |
|        4 | Wanda      | song and dance   |      200.00 |
|        5 | Ann        | trained squirrel |       79.99 |
+----------+------------+------------------+-------------+
5 rows in set (0.00 sec)

たとえば、キャサリンの名前のつづりが間違っていることに気付いたとします。名前は「K」で始まるはずですが、表では「C」で始まるので、次のUPDATEを実行してその値を変更することにします。声明。 この操作は、Catherineという名前の行のname値をKatherineに変更することにより、name列の値を更新します。

UPDATE clients
SET name = 'Katherine'
WHERE name = 'Catherine';

Output
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

この出力は、1つの行のみが更新されたことを示しています。 これは、前のSELECTクエリを再度実行することで確認できます。

SELECT * FROM clients;

Output
+----------+------------+------------------+-------------+
| clientID | name       | routine          | standardFee |
+----------+------------+------------------+-------------+
|        1 | Gladys     | song and dance   |      180.00 |
|        2 | Katherine  | standup          |       99.99 |
|        3 | Georgeanna | standup          |       45.00 |
|        4 | Wanda      | song and dance   |      200.00 |
|        5 | Ann        | trained squirrel |       79.99 |
+----------+------------+------------------+-------------+
5 rows in set (0.00 sec)

この出力は、以前はCatherineと表示されていた値が実際にKatherineに変更されたことを示しています。

この例では、name列の値を1つだけ更新しました。 ただし、排他的でないWHERE句を使用して、複数の値を更新できます。

説明のために、スタンダップコメディや歌と踊りのルーチンを実行するすべてのクライアントに対して標準の成功報酬を交渉するとします。 次のステートメントは、standardFee列の値を、140と等しく設定することによって更新します。

この例のWHERE句にはLIKE演算子が含まれているため、routine値が指定されたワイルドカードと一致する各クライアントのperformanceFee値のみが更新されることに注意してください。パターン、's%'。 つまり、ルーチンが文字「s」で始まるすべてのパフォーマーの成功報酬を更新します。

UPDATE clients
SET standardFee = 140
WHERE routine LIKE 's%';

Output
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

clientsテーブルの内容をもう一度クエリすると、結果セットは、4つのクライアントが同じ成功報酬を持っていることを確認します。

SELECT * FROM clients;

Output
+----------+------------+------------------+-------------+
| clientID | name       | routine          | standardFee |
+----------+------------+------------------+-------------+
|        1 | Gladys     | song and dance   |      140.00 |
|        2 | Katherine  | standup          |      140.00 |
|        3 | Georgeanna | standup          |      140.00 |
|        4 | Wanda      | song and dance   |      140.00 |
|        5 | Ann        | trained squirrel |       79.99 |
+----------+------------+------------------+-------------+
5 rows in set (0.00 sec)

テーブル内のいずれかの列に数値が含まれている場合は、SET句の算術演算を使用してそれらを更新できます。 たとえば、クライアントの成功報酬ごとに40％の増額を交渉するとします。 これをclientsテーブルに反映するには、次のようなUPDATE操作を実行できます。

UPDATE clients
SET standardFee = standardFee * 1.4;

Output
Query OK, 5 rows affected, 1 warning (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 1

SHOW WARNINGS;

Output
+-------+------+--------------------------------------------------+
| Level | Code | Message                                          |
+-------+------+--------------------------------------------------+
| Note  | 1265 | Data truncated for column 'standardFee' at row 5 |
+-------+------+--------------------------------------------------+
1 row in set (0.00 sec)

clientsテーブルをもう一度クエリして、各クライアントの成功報酬が40％増加したことを確認します。

SELECT * FROM clients;

Output
+----------+------------+------------------+-------------+
| clientID | name       | routine          | standardFee |
+----------+------------+------------------+-------------+
|        1 | Gladys     | song and dance   |      196.00 |
|        2 | Katherine  | standup          |      196.00 |
|        3 | Georgeanna | standup          |      196.00 |
|        4 | Wanda      | song and dance   |      196.00 |
|        5 | Ann        | trained squirrel |      111.99 |
+----------+------------+------------------+-------------+
5 rows in set (0.00 sec)

前述のように、単一のUPDATEステートメントを使用して複数の列のデータを更新することもできます。 これを行うには、更新するすべての列を指定し、それぞれにそれぞれの値式を続けてから、各列と値式のペアをコンマで区切る必要があります。

たとえば、クライアントが演奏する会場で、GeorgeannaとWandaのすべてのショーの参加者数が誤って報告されていたことがわかったとします。 偶然にも、あなたは彼らの公演ごとに間違ったチケット価格を入力したこともありました。

showsテーブルのデータを更新する前に、次のクエリを実行して、現在そのテーブルに保持されているすべてのデータを取得します。

SELECT * FROM shows;

Output
+--------+------------+----------+------------+-------------+
| showID | showDate   | clientID | attendance | ticketPrice |
+--------+------------+----------+------------+-------------+
|      1 | 2019-12-25 |        4 |        124 |       15.00 |
|      2 | 2020-01-11 |        5 |         84 |       29.50 |
|      3 | 2020-01-17 |        3 |        170 |       12.99 |
|      4 | 2020-01-31 |        5 |        234 |       14.99 |
|      5 | 2020-02-08 |        1 |         86 |       25.00 |
|      6 | 2020-02-14 |        3 |        102 |       39.50 |
|      7 | 2020-02-15 |        2 |        101 |       26.50 |
|      8 | 2020-02-27 |        2 |        186 |       19.99 |
|      9 | 2020-03-06 |        4 |        202 |       30.00 |
|     10 | 2020-03-07 |        5 |        250 |        8.99 |
+--------+------------+----------+------------+-------------+
10 rows in set (0.01 sec)

実際の数と価格を反映するには、テーブルを更新して、各パフォーマンスに20人の参加者を追加し、各ticketPrice値を50％増やします。 次のような操作でこれを行うことができます。

UPDATE shows
SET attendance = attendance + 20,
ticketPrice = ticketPrice * 1.5
WHERE clientID IN 
(SELECT clientID
FROM clients
WHERE name = 'Georgeanna' OR name = 'Wanda');

Output
Query OK, 4 rows affected, 1 warning (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 1

この例では、WHERE句でサブクエリを使用して、clientsテーブルからGeorgeannaとWandaのclientID値を返すことに注意してください。 多くの場合、識別番号などの抽象的な値は覚えにくい場合がありますが、サブクエリを使用して値を見つけるこの方法は、問題のレコードに関する特定の属性しか知らない場合に役立ちます。

showsテーブルを更新した後、もう一度クエリを実行して、変更が期待どおりに行われたことを確認します。

SELECT * FROM shows;

Output
+--------+------------+----------+------------+-------------+
| showID | showDate   | clientID | attendance | ticketPrice |
+--------+------------+----------+------------+-------------+
|      1 | 2019-12-25 |        4 |        144 |       22.50 |
|      2 | 2020-01-11 |        5 |         84 |       29.50 |
|      3 | 2020-01-17 |        3 |        190 |       19.49 |
|      4 | 2020-01-31 |        5 |        234 |       14.99 |
|      5 | 2020-02-08 |        1 |         86 |       25.00 |
|      6 | 2020-02-14 |        3 |        122 |       59.25 |
|      7 | 2020-02-15 |        2 |        101 |       26.50 |
|      8 | 2020-02-27 |        2 |        186 |       19.99 |
|      9 | 2020-03-06 |        4 |        222 |       45.00 |
|     10 | 2020-03-07 |        5 |        250 |        8.99 |
+--------+------------+----------+------------+-------------+
10 rows in set (0.00 sec)

この場合も、この出力はUPDATEステートメントが正常に完了したことを示しています。

JOIN句を使用して複数のテーブルのデータを更新する

これまで、このガイドでは、一度に1つのテーブルのデータを更新する方法のみを示してきました。 ただし、一部のSQL実装では、テーブルをJOIN句と一時的に組み合わせることにより、複数のテーブルの複数の列を更新できます。

JOIN句を使用して複数のテーブルを更新するために使用できる一般的な構文は次のとおりです。

UPDATE table_1 JOIN table_2
ON table_1.related_column = table_2.related_column
SET table_1.column_name = value_expression,
table_2.column_name = value_expression
WHERE conditions_apply;

この構文例は、UPDATEキーワードで始まり、JOIN句で区切られた2つのテーブルの名前が続きます。 続いてON句があり、クエリが2つのテーブルを結合する方法を説明しています。

ほとんどの実装では、SQL標準で「JOIN適格」データ型と呼ばれる列のセット間の一致を見つけることでテーブルを結合できます。 つまり、一般に、数値データを保持する任意の列を、それぞれのデータ型に関係なく、数値データを保持する他の列と結合できます。 同様に、文字値を保持する任意の列を、文字データを保持する他の列と結合できます。

JOIN句は複数のテーブルの内容を比較するため、この構文例では、列の名前の前にテーブルの名前とピリオドを付けて、各列を選択するテーブルを指定します。 これは、完全修飾列参照として知られています。 このように、どの操作でも列を選択するテーブルを指定できますが、これは通常、複数のテーブルを操作するときの明確さを向上させるためにのみ使用されます。

前に作成したサンプルテーブルで説明するには、次のUPDATEステートメントを実行します。 これにより、clientsテーブルとshowsテーブルがそれぞれのclientID列で結合され、GladysのレコードのroutineとticketPriceの値が更新されます。 clientsテーブルとshowsテーブルにリストされている彼女の各パフォーマンス：

UPDATE clients JOIN shows
USING (clientID)
SET clients.routine = 'mime',
shows.ticketPrice = 30
WHERE name = 'Gladys';

Output
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

この例では、前の構文例で使用したONキーワードではなく、USINGキーワードを使用してテーブルを結合していることに注意してください。 これが可能なのは、両方のテーブルに同様のデータ型を共有するclientID列があるためです。

JOIN操作の詳細なチュートリアルについては、SQLでの結合の使用方法に関するガイドを参照してください。

外部キーの変更UPDATEの動作

デフォルトでは、FOREIGN KEY制約との競合を引き起こすUPDATEステートメントは失敗します。

MySQLへの接続と前提条件のサンプルデータベースセクションのセットアップから、showsテーブルのclientID列がclientID列。 つまり、showsテーブルのclientID列に入力された値は、clientsテーブルの値にすでに存在している必要があります。

showsテーブルのclientID列にも表示されるclientsテーブルのレコードのclientID値を更新しようとすると、次のようになります。エラー：

UPDATE clients
SET clientID = 9
WHERE name = 'Ann';

Output
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

このエラーを回避するには、既存の外部キー制約をUPDATE操作を異なる方法で処理する制約に置き換えます。

現在の制約を置き換えるには、最初にALTER TABLEステートメントで制約を削除する必要があります。 showsのCREATE TABLEステートメントで、テーブルのFOREIGN KEY制約の名前としてclient_fkを定義したことを思い出してください。

ALTER TABLE shows
DROP FOREIGN KEY client_fk;

Output
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

その後、特定のユースケースに適した方法でUPDATE操作を処理するように構成された新しい外部キー制約を作成します。 外部キーに違反するUPDATEステートメントを禁止するデフォルト設定の他に、ほとんどのRDBMSで使用可能な他の2つのオプションがあります。

• ON UPDATE SET NULL：このオプションを使用すると、親テーブルからレコードを更新し、それらをNULLとして参照する子テーブルの値をリセットできます。
• ON UPDATE CASCADE：親テーブルの行を更新すると、このオプションにより、SQLは、親テーブルの新しい値と一致するように、子テーブルでその行を参照するすべてのレコードを自動的に更新します。

この例では、ON UPDATE SET NULLは意味がありません。 結局のところ、クライアントの識別番号の1つを変更しても、clientsテーブルから削除しない場合でも、showsテーブルのパフォーマンスに関連付ける必要があります。 彼らの新しい識別番号は彼らのパフォーマンスの記録に反映されるべきであるため、ON UPDATE CASCADEオプションは私たちの目的にとってより理にかなっています。

ON UPDATE CASCADEの動作に続くFOREIGN KEY制約を追加するには、次のALTER TABLEステートメントを実行します。 これにより、new_client_fkという名前の新しい制約が作成されます。これは、以前の制約定義を複製しますが、ON UPDATE CASCADEオプションが含まれています。

ALTER TABLE shows
ADD CONSTRAINT new_client_fk
FOREIGN KEY (clientID)
REFERENCES clients (clientID)
ON UPDATE CASCADE;

Output
Query OK, 10 rows affected (0.02 sec)
Records: 10  Duplicates: 0  Warnings: 0

この出力は、操作がshowsテーブルの10行すべてに影響を与えたことを示しています。

CREATE TABLE shows
(showID int PRIMARY KEY,
showDate date,
clientID int,
attendance int,
ticketPrice decimal (4,2),
CONSTRAINT client_fk
FOREIGN KEY (clientID)
REFERENCES clients(clientID)
ON UPDATE CASCADE
);

その後、clientsテーブルの任意のレコードのclientID値を更新できます。これらの変更は、showsテーブルの任意の行にカスケードされます。それを参照してください：

UPDATE clients
SET clientID = 9
WHERE name = 'Ann';

Output
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

この出力は、1つの行にのみ影響することを示していますが、Annに関連付けられたshowsテーブルのパフォーマンスレコードのclientID値も更新されます。 これを確認するには、次のクエリを実行して、showsテーブルからすべてのデータを取得します。

SELECT * FROM shows;

Output
+--------+------------+----------+------------+-------------+
| showID | showDate   | clientID | attendance | ticketPrice |
+--------+------------+----------+------------+-------------+
|      1 | 2019-12-25 |        4 |        144 |       22.50 |
|      2 | 2020-01-11 |        9 |         84 |       29.50 |
|      3 | 2020-01-17 |        3 |        190 |       19.49 |
|      4 | 2020-01-31 |        9 |        234 |       14.99 |
|      5 | 2020-02-08 |        1 |         86 |       30.00 |
|      6 | 2020-02-14 |        3 |        122 |       59.25 |
|      7 | 2020-02-15 |        2 |        101 |       26.50 |
|      8 | 2020-02-27 |        2 |        186 |       19.99 |
|      9 | 2020-03-06 |        4 |        222 |       45.00 |
|     10 | 2020-03-07 |        9 |        250 |        8.99 |
+--------+------------+----------+------------+-------------+
10 rows in set (0.00 sec)

予想どおり、clientsテーブルのclientID列に加えられた更新は、showsテーブルの関連する行にカスケードされました。

結論

このガイドを読むことで、SQLのUPDATEステートメントを使用して複数のテーブルの既存のレコードを変更する方法を学びました。 また、SQLが外部キー制約と競合するUPDATE操作を処理する方法、およびそのデフォルトの動作を変更する方法についても学びました。

ここで概説するコマンドは、SQLを使用するすべてのデータベース管理システムで機能するはずです。 すべてのSQLデータベースは独自の言語実装を使用しているため、UPDATE操作の処理方法と使用可能なオプションの詳細については、DBMSの公式ドキュメントを参照してください。

SQLの操作について詳しく知りたい場合は、SQLの使用方法に関するこのシリーズの他のチュートリアルを確認することをお勧めします。