INSERT・UPDATE・DELETE文とは ― SQLのデータ更新

INSERT文は表に新しい行を追加し、UPDATE文は既存の行の値を書き換え、DELETE文は既存の行を削除するSQL文です。どれもDML（データ操作言語）に属し、表の中身を変えます。

SELECT文は表を読むだけで、中身を変えません。UPDATE文とDELETE文は、WHEREを付け忘れると表の全行が対象になります。実行の前に対象の行を確かめる方法と、間違えたときにROLLBACKで戻す方法も覚えておくと安心です。

次の社員表を例にします。社員IDが主キーで、部署IDは部署表（D1 営業・D2 総務・D3 開発）を参照する外部キーです。氏名にはNOT NULLが付いています。

CREATE TABLE 社員 (
  社員ID  INTEGER PRIMARY KEY,
  氏名    VARCHAR(20) NOT NULL,
  部署ID  CHAR(2) REFERENCES 部署 (部署ID),
  給与    INTEGER
);

INSERT文は、INSERT INTOの後に表の名前と列名を書き、VALUESの後に入れる値を書きます。列名と値は、書いた順に1つずつ対応します。文字列の値は、シングルクォートで囲みます。

INSERT INTO 社員 (社員ID, 氏名, 部署ID, 給与)
VALUES (105, '伊藤', 'D2', 260000);

列名を省略した列には、既定値かNULLが入る

値が決まっていない列は、列名ごと省けます。省いた列には、CREATE TABLEで定義した既定値が入り、既定値が無ければNULLが入ります。

INSERT INTO 社員 (社員ID, 氏名)
VALUES (106, '渡辺');
-- 部署IDと給与はNULLになる

既定値の無いNOT NULLの列を省くと、NULLを入れることになるのでエラーになります。例の表では、氏名を省いたINSERT文が拒否されます。

表の名前の後の列名は、まとめて省くこともできます。その場合は、表の定義の順に値を並べます。ただし、列の順番を覚えていないと書けません。PostgreSQLの公式ドキュメントにも、列名をいつも書くのがよい習慣だと考える利用者が多いとあります。

INSERT INTO 社員
VALUES (105, '伊藤', 'D2', 260000);

複数の行は、VALUESの後にカンマで並べる

1つのINSERT文で、複数の行をまとめて追加できます。VALUESの後に、行ごとの値のかたまりをカンマで区切って並べます。

INSERT INTO 社員 (社員ID, 氏名, 部署ID, 給与)
VALUES (105, '伊藤', 'D2', 260000),
       (106, '渡辺', 'D3', 270000);

INSERT ... SELECTは、問い合わせの結果を別の表に入れる

VALUESの代わりにSELECT文を書くと、その結果の行をまとめて追加できます。次の例は、社員ID 104の行から社員IDと氏名を取り出し、退職者表に入れます。

INSERT INTO 退職者 (社員ID, 氏名)
SELECT 社員ID, 氏名
FROM 社員
WHERE 社員ID = 104;

SELECTが返す列の数と並びは、INSERTの列名と合わせます。SELECTの結果が0行なら、追加される行も0行で、エラーにはなりません。コピー元の社員表は変わりません。

UPDATE文は、UPDATEの後に表の名前を書き、SETの後に列名と新しい値を書きます。どの行を書き換えるかは、WHERE句の条件で決まります。次の例は、社員ID 102の部署IDをD1に変えます。

UPDATE 社員
SET 部署ID = 'D1'
WHERE 社員ID = 102;

SETの「=」は値を入れる意味で、WHEREの「=」は等しいかどうかを比べる意味です。複数の列を一度に書き換えるときは、SETの後に「列名 = 値」をカンマで並べます。SETに書いていない列は変わりません。

UPDATE 社員
SET 部署ID = 'D1', 給与 = 290000
WHERE 社員ID = 102;

いまの値をもとに計算して書き換える

新しい値には、計算式も書けます。式の中の列名は、その行のいまの値を指します。次の例は、部署IDがD1の行だけ、給与を10000増やします。

UPDATE 社員
SET 給与 = 給与 + 10000
WHERE 部署ID = 'D1';

行ごとに違う値を1つの文で書き込むときは、SETにCASE式を書きます。

WHERE句の条件には、サブクエリも書けます。次の文は、部署名が営業の部署に属する行の給与を増やします。結果は、上の例と同じ2行になります。

UPDATE 社員
SET 給与 = 給与 + 10000
WHERE 部署ID IN (SELECT 部署ID FROM 部署
                 WHERE 部署名 = '営業');

表だけでなく、ビューにUPDATE文を実行できる場合もあります。どんなビューなら更新できるかは、ビューの記事で説明しています。プログラムが1行ずつ読んだ行をその場で書き換える方法は、カーソルの記事で説明しています。行を変えたときに別の表への記録などの処理を自動で実行するには、トリガを使います。

DELETE文は、DELETE FROMの後に表の名前を書き、WHERE句で消す行を指定します。行は丸ごと消え、1つの列の値だけを消すことはできません。値だけを空にしたいときは、UPDATE文でNULLを入れます。

DELETE FROM 社員
WHERE 社員ID = 104;

DELETE・TRUNCATE・DROP TABLEの違い

表のデータを消す文には、DELETEのほかにTRUNCATEとDROP TABLEがあります。違いは、消す範囲と、表の定義が残るかどうかです。

TRUNCATEは、WHEREの無いDELETEと同じくすべての行を消します。表を1行ずつ読まないので、大きな表では速く終わります。ほかの表の外部キーから参照されている表は、PostgreSQLでもMySQLでも、そのままではTRUNCATEできません。

取り消せるかどうかは製品によって違います。PostgreSQLでは、BEGINで始めたトランザクションの中なら、TRUNCATEもDROP TABLEもコミットする前にROLLBACKで取り消せます。MySQLのマニュアルはTRUNCATE TABLEをDDLに分類しています。DROP TABLEと同じく暗黙にコミットされるので、取り消せません。

次の図で例の社員表に実行する文を選ぶと、実行後の表と、変わった行の数が切り替わります。図は実際のデータベースには接続していません。結果は、PostgreSQLで同じ文を実行して確かめたものです。

INSERT INTO 社員 (社員ID, 氏名, 部署ID, 給与)
VALUES (105, '伊藤', 'D2', 260000);

UPDATE 社員
SET 部署ID = 'D1'
WHERE 社員ID = 102;

UPDATE 社員
SET 給与 = 給与 + 10000
WHERE 部署ID = 'D1';

UPDATE 社員
SET 給与 = 給与 + 10000;

DELETE FROM 社員
WHERE 社員ID = 104;

DELETE FROM 社員;

INSERT INTO 社員 (社員ID, 氏名, 部署ID, 給与)
VALUES (101, '伊藤', 'D2', 260000);

INSERT INTO 社員 (社員ID, 氏名, 部署ID, 給与)
VALUES (105, '伊藤', 'D9', 260000);

UPDATE文とDELETE文は、WHERE句を書かないと表のすべての行が対象になります。文法としては正しいので、エラーにはなりません。DELETE FROM 社員; は、社員表を空にします。

逆に、WHEREの条件に合う行が1行も無くても、エラーにはならず、変わる行が0行になるだけです。どちらの場合も、文が成功したことしか分からないので、変わった行の数を確かめます。

DBMSは、実行後に処理した行の数を返します。PostgreSQLでは、UPDATE 2 や DELETE 1 のように、条件に合った行の数が表示されます。MySQLのUPDATEは、値が実際に変わった行の数を返します。数が予定と違えば、条件を見直します。

実行する前に、同じWHEREでSELECTする

書き換える前に、同じWHERE句を付けたSELECT文を実行すると、対象の行を先に確かめられます。件数だけを知りたいときは、COUNT(*)を使います。

SELECT COUNT(*) FROM 社員 WHERE 部署ID = 'D1';
-- 2 が返る。予定どおり2行なら UPDATE を実行する

WHERE句に利用者の入力をそのまま埋め込むと、条件を書き換えられることがあります。仕組みと対策は、SQLインジェクションとXSSの違いの記事で説明しています。

DBMSは、行を追加・書き換え・削除するたびに、表の制約が守られているかを確かめます。違反する文はエラーになり、表の中身は実行前のままです。例の社員表では、次のような文が拒否されます。

• 主キーの重複：社員ID 101の行をもう1行INSERTする
• 参照制約の違反：部署表に無い部署ID D9でINSERTする
• 非NULL制約の違反：氏名を省いてINSERTする
• 参照されている行の削除：社員が属している部署D3を部署表からDELETEする

UPDATE文でも主キーは重複できません。UPDATE 社員 SET 社員ID = 200 WHERE 部署ID = 'D1'; は、2行が同じ200になるのでエラーになります。1行目だけが書き換わることはなく、どの行も変わりません。

複数の行を入れるINSERT文も同じです。PostgreSQLでは、1行でも違反があれば文全体がエラーになり、ほかの行も追加されません。MySQLのInnoDBでも、IGNOREを付けなければ、主キーの重複でエラーになった文は文ごと取り消されます。

制約の種類と書き方は、CREATE TABLEと制約の記事で説明しています。

コミットとは、変更を確定させることです。PostgreSQLもMySQLも、初期設定では1つの文が成功するたびに自動でコミットします。そのため、そのままDELETE文を実行すると、その場で削除が確定します。

BEGINで始めると、COMMITを実行するまで変更は確定しません。途中で間違いに気づいたら、ROLLBACKでBEGINの後の変更をすべて取り消せます。

BEGIN;
DELETE FROM 社員;   -- WHEREを付け忘れた
ROLLBACK;           -- 4行とも元に戻る

関係する複数の文は、1つのトランザクション（まとめて確定または取り消しする処理の単位）にします。次の例では、退職者表に写す文と社員表から消す文が、両方反映されるか両方取り消されるかのどちらかになります。片方だけが反映されて、社員の行がどこにも無い状態にはなりません。

BEGIN;
INSERT INTO 退職者 (社員ID, 氏名)
  SELECT 社員ID, 氏名 FROM 社員 WHERE 社員ID = 104;
DELETE FROM 社員 WHERE 社員ID = 104;
COMMIT;

コミットとロールバックがACID特性のどれを支えるかは、トランザクションの記事で説明しています。

• PostgreSQL公式ドキュメント「Inserting Data」「Updating Data」「Deleting Data」「TRUNCATE」「BEGIN」
• MySQL 8.4 リファレンスマニュアル「TRUNCATE TABLE Statement」「autocommit, Commit, and Rollback」「InnoDB Error Handling」