データを変える：INSERT・UPDATE・DELETE

INSERT、UPDATE、DELETE は保存済みのデータを変更します。基本の書き方に加えて、対象行の確認と変更の取り消し方も扱います。

このレッスンはデータを変更します。変わるのはあなたのコピーです。サンプルデータベースはブラウザに読み込まれ、そこから出ていかないので、ここでは何を実行しても、失敗しても安全です。やり直したいときはウェルカム画面からサンプルを読み込み直してください。

INSERT

列名を書き、それぞれに値を与えます。書かなかった列には既定値が入ります。ここでは added_at が現在時刻になります。

1

INSERT INTO wishlists (customer_id, product_id)
VALUES (1, 288);
Planche で実行

1 行を変更

ブラウザの別タブで Planche が開き、サンプルデータベースを読み込んだ状態でこのクエリが入ります。このページの例はどれも同じタブを使い回します。F9 を押すまで実行はされません。

1行変更。テーブルの列順に頼らず列名を明示して書くことが、誰かが列を1つ足した後もこの文が動き続ける理由になります。

データベースが拒否するもの

まったく同じ文をもう一度実行してみてください。

2

-- The same statement a second time. (customer_id, product_id) is the
-- primary key of wishlists, so the database refuses the duplicate.
INSERT INTO wishlists (customer_id, product_id)
VALUES (1, 288);
Planche で実行

エラー

UNIQUE constraint failed: wishlists.customer_id, wishlists.product_id

エラーになり、何も変わりません。wishlists には PRIMARY KEY (customer_id, product_id) があるので、同じ組み合わせは2つ存在できません。制約が拒否したのです。制約は回避すべき障害物ではありません。アプリのバグが、永久に残る誤ったデータに変わるのを止めているものです。

UPDATE と、いちばん大事な句

3

UPDATE customers
SET loyalty_points = loyalty_points + 100
WHERE id = 1;
Planche で実行

1 行を変更

2つの習慣でこれはかなり起きにくくなります。ひとつは SET より先に WHERE を書くこと。もうひとつは、同じ FROM と同じ WHERE で SELECT として実行し、何が返るかを見てから変更することです。

2つめは Planche に任せられます。設定 › 安全性 › 影響行のプレビューを有効にすると、UPDATE や DELETE は対応する SELECT に書き換えられ、件数を数えて実行前に見せてくれます。読み取り専用の接続や、本番接続に色を付ける設定も同じ場所にあります。

4

SELECT id, first_name, last_name, loyalty_points
FROM customers
WHERE id = 1;
Planche で実行

結果 — 1 行

idfirst_namelast_nameloyalty_points
1LiamFischer3619

DELETE

5

DELETE FROM wishlists
WHERE customer_id = 1
  AND product_id = 288;
Planche で実行

1 行を変更

サブクエリで対象行を選ぶ

UPDATE や DELETE の WHERE は普通の WHERE なので、サブクエリのレッスンの内容がそのまま使えます。別のテーブルの内容をもとに、あるテーブルの行を変えるときの書き方です。

6

-- Which rows to change is its own question, and a subquery can answer it.
UPDATE customers
SET loyalty_points = loyalty_points + 50
WHERE id IN (
  SELECT customer_id
  FROM orders
  WHERE status = 'refunded'
    AND total > 2000
);
Planche で実行

242 行を変更

2,000を超える返金注文を持つ顧客が242人いて、その242行が変わりました。さっきの習慣がそのまま当てはまります。まず SELECT id FROM customers WHERE id IN (...) として実行し、件数を数えてください。間違いが潜むのはサブクエリだからです。

7

DELETE FROM wishlists
WHERE product_id IN (
  SELECT id FROM products WHERE stock_quantity = 0
);
Planche で実行

260 行を変更

NOT EXISTS と EXISTS もここで使えますし、否定形についてはサブクエリのレッスンで述べた理由から、そちらが安全です。一方で移植性がないのは、UPDATE や DELETE の中で直接結合する書き方です。エンジンごとに綴りが違い、そもそもできないエンジンもあります。専用の方言ページで5つすべてを扱います。

トランザクション

トランザクションは複数の文をひとまとめにし、全部起きるか何も起きないかにします。そしてコミットするまでは、結果を見て気を変えることもできます。

8

BEGIN;

UPDATE coupons
SET used_count = used_count + 1
WHERE code = 'WELCOME10';

SELECT code, used_count FROM coupons WHERE code = 'WELCOME10';

ROLLBACK;
Planche で実行

結果 — 1 行

codeused_count
WELCOME1063

BEGIN で開始し、SELECT ではクーポンがすでに増えて見え、ROLLBACK で元に戻ります。ROLLBACK を COMMIT に変えれば、変更は確定します。件数が戻っていることを自分で確かめてください。

9

SELECT code, used_count FROM coupons WHERE code = 'WELCOME10';
Planche で実行

結果 — 1 行

codeused_count
WELCOME1062

本番データベースで危ない変更をするときは、これが正しい道具です。トランザクションを開き、文を実行し、行数が想定どおりか確かめ、それから初めてコミットします。Planche にはモードとして用意してあります。接続 › 手動トランザクションにするとトランザクションを開いたままにし、ツールバーにコミットとロールバックが出るので、明示するまではどの文も仮のものになります。

ここまでで書けるもの

クエリが遅い理由を突き止めること以外は、ひととおりできます。それが最後のレッスンです。

自分で書いてみる

  1. 在庫切れの商品をすべて販売停止にしてください。実行する前に、何行変わるはずかを確かめること。

    答えを見る

    答えを見る

    UPDATE products
SET is_active = 0
WHERE stock_quantity = 0;
    Planche で実行

    40 行を変更

    40行です。先に SELECT COUNT(*) FROM products WHERE stock_quantity = 0 を実行するのが習慣のすべてです。文を打つ前に数を知っていれば、想定と違う件数は発見ではなく警告になります。

  2. 既読で、2025年より古い通知を削除してください。

    答えを見る

    答えを見る

    DELETE FROM notifications
WHERE is_read = 1
  AND created_at < '2025-01-01';
    Planche で実行

    1202 行を変更

    1,202行です。条件は両方とも要ります。is_read = 1 を落とすと未読の通知まで消えますし、これは DELETE なので、先にトランザクションを開いていない限り取り消す手段はありません。