SQLite UPDATE文の使い方 | WHERE・複数列・UPDATE FROM

UPDATE で既存の行を書き換える

INSERT が新しい行を追加するのに対して、UPDATE はすでに存在する行を書き換えるための文です。SQLite の update 文は構文がシンプルなので、まずは形ごと覚えてしまうのがおすすめです。

UPDATE table_name
SET column = value
WHERE condition;

実際に動かしてみる例:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT,
    status TEXT DEFAULT 'active'
);

INSERT INTO users (name, email) VALUES
    ('Ada', 'ada@old.com'),
    ('Boris', 'boris@old.com'),
    ('Rosa', 'rosa@old.com');

UPDATE users
SET email = 'ada@new.com'
WHERE id = 1;

SELECT * FROM users;

SET で何を変えるかを指定し、WHERE でどの行を対象にするかを決めます。それ以外の行はそのまま残ります。

実務では WHERE を省略してはいけない

文法上 WHERE はオプションです。ただ、実務でこれを省略すると、若手エンジニアが午後を丸ごと潰すパターンに直行します。

UPDATE users SET status = 'inactive';
-- これで全てのユーザーが非アクティブになりました

フィルタを書かなければ、すべての行がマッチします。SQLiteは何も言わずにそのまま実行してしまいます。だからこそ、まず WHERE を書いてから SET を書く——この習慣をつけておくだけで、事故をぐっと減らせます。

WHERE の条件に自信がないときは、まず同じ条件で SELECT を流して確認しましょう。

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, status TEXT);
INSERT INTO users VALUES (1, 'Ada', 'active'), (2, 'Boris', 'active'), (3, 'Rosa', 'pending');

-- どの行が変更されるかをプレビューする。
SELECT id, name, status FROM users WHERE status = 'pending';

-- 同じ WHERE 句で UPDATE を実行する。
UPDATE users SET status = 'active' WHERE status = 'pending';

SELECT * FROM users;

同じ条件で SELECT と UPDATE を並べて書いておけば、SELECT の方がそのままドライランとして使えます。

複数カラムをまとめて更新する

SET の中はカンマ区切りで複数の代入を並べられます。SET は1つでOK、カラムはいくつでも指定できます:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL,
    stock INTEGER
);

INSERT INTO products VALUES (1, 'Widget', 9.99, 100);

UPDATE products
SET price = 12.99,
    stock = 75,
    name = 'Premium Widget'
WHERE id = 1;

SELECT * FROM products;

1往復のクエリで1行を変更し、3つのカラムをまとめて更新できます。1つで済むところを、わざわざ3本のUPDATE文に分けて書く必要はありません。

=の右辺に式を書く

=の後ろにはリテラルだけでなく、任意の式を指定できます。もちろん、更新対象のカラム自身の現在値を参照することも可能です。

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL, stock INTEGER);
INSERT INTO products VALUES
    (1, 'Widget', 10.00, 50),
    (2, 'Gadget', 20.00, 30),
    (3, 'Gizmo', 15.00, 80);

-- 価格を10%引き上げる。
UPDATE products
SET price = price * 1.10;

-- 1つの行の在庫を減らす。
UPDATE products
SET stock = stock - 1
WHERE id = 2;

SELECT * FROM products;

price * 1.10 は現在の価格を読み取り、1.10 倍した結果を書き戻します。SQLite は右辺を評価するときに、この文による更新が適用される 前 の行の値を使うため、複数のカラムを参照しても安全です。

UPDATE products SET price = price * 1.10, stock = stock + price;
-- ここでの右辺の 'price' は、更新後の価格ではなく、更新前の古い価格です。

UPDATE ... FROM:別テーブルから値を引っ張ってくる

SQLite 3.33以降では、UPDATE文でFROM句が使えるようになりました。テーブル間でデータを同期させたいときに、これがいちばんスッキリ書ける方法です。

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, total_spent REAL DEFAULT 0);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, amount REAL);

INSERT INTO customers (name) VALUES ('Ada'), ('Boris'), ('Rosa');
INSERT INTO orders (customer_id, amount) VALUES
    (1, 50.00), (1, 30.00),
    (2, 100.00),
    (3, 25.00), (3, 75.00);

UPDATE customers
SET total_spent = totals.sum_amount
FROM (
    SELECT customer_id, SUM(amount) AS sum_amount
    FROM orders
    GROUP BY customer_id
) AS totals
WHERE customers.id = totals.customer_id;

SELECT * FROM customers;

サブクエリで顧客ごとの合計を計算し、外側の UPDATE がその結果を id で customers に結合し直しています。UPDATE ... FROM を使わない場合、列ごとに相関サブクエリを書く羽目になり、コードがかなりごちゃつきます。

押さえておきたいポイントをいくつか挙げておきます。

• 対象テーブルは UPDATE の直後に書きます。FROM 句には 入れません 。
• 結合条件は WHERE 句で指定します。ここでは ON キーワードは使いません。
• FROM 側で複数行にマッチする可能性がある結合は、結果が不定になります。結合キーは、ターゲット行に対して最大1件しか一致しないように設計してください。

RETURNING句で更新結果を取得する

SQLite 3.35以降では、UPDATE 文の中で更新後の行をそのまま返せるようになりました。アプリ側で更新後の値が必要なときに、わざわざ SELECT を追加で投げなくて済むので便利です。

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL);
INSERT INTO products VALUES (1, 'ウィジェット', 10.00), (2, 'ガジェット', 20.00);

UPDATE products
SET price = price * 0.9
WHERE price > 15
RETURNING id, name, price;

変更が実際に適用された行を、更新後の値ごとそのまま受け取れます。ラウンドトリップを減らせるうえに、並行処理でありがちな競合状態の一部もきれいに排除できます。RETURNING については、本章のあとのページでまるごと一本取り上げます。

UPDATE OR REPLACE: 制約違反への対処

UNIQUE 制約に引っかかるような更新を実行すると、デフォルトでは文ごとエラーで中断されます。OR 句を使えば、この挙動を別のポリシーに差し替えられます。

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE,
    name TEXT
);

INSERT INTO users VALUES
    (1, 'ada@x.com', 'Ada'),
    (2, 'boris@x.com', 'Boris');

-- これは UNIQUE 制約違反で失敗します:
-- UPDATE users SET email = 'boris@x.com' WHERE id = 1;

-- OR REPLACE は競合する行を削除してから更新を実行します。
UPDATE OR REPLACE users
SET email = 'boris@x.com'
WHERE id = 1;

SELECT * FROM users;

オプションは OR ABORT（デフォルト）、OR REPLACE、OR IGNORE、OR FAIL、OR ROLLBACK の5つです。要注意なのは REPLACE で、競合した行を削除してしまうため、外部キー経由で連鎖的に影響が広がることもあります。「同じユニーク値の行がすでにあれば、古いほうは捨てる」と本当に言い切れるときだけ使ってください。

UPSERT的な処理がしたいなら、専用の INSERT ... ON CONFLICT 構文のほうが意図が伝わりやすいです。こちらは別ページで解説しています。

sqlite 一括更新はトランザクションで安全に

大量の行を書き換えるときや、複数の UPDATE 文をまとめて成功させたいときは、トランザクションで囲むのが鉄則です。途中で何か問題が起きても、実行前の状態にロールバックできます。

CREATE TABLE accounts (id INTEGER PRIMARY KEY, name TEXT, balance REAL);
INSERT INTO accounts VALUES (1, 'Ada', 100.00), (2, 'Boris', 50.00);

BEGIN;
    UPDATE accounts SET balance = balance - 25 WHERE id = 1;
    UPDATE accounts SET balance = balance + 25 WHERE id = 2;
COMMIT;

SELECT * FROM accounts;

2 つ目の文が失敗したら（たとえば制約に引っかかったら）、ROLLBACK で 1 つ目もまとめて取り消されます。トランザクションを使わなければ、Ada から 25 引かれただけで Boris には届かない、という中途半端な送金状態が残ってしまうわけです。トランザクションについては後の章でじっくり扱いますが、ひとまず「一括更新はほぼ確実にトランザクションの中で実行する」と覚えておいてください。

よくある落とし穴

地味にハマりやすいポイントをまとめておきます。

• WHERE を書き忘れる — 全行が更新されます。実行前に文を声に出して読むくらいの慎重さでちょうどいいです。
• WHERE で演算子を間違える — WHERE status = NULL は何にもマッチしません。IS NULL を使いましょう。詳しくは演算子のページで解説します。
• 1 行返るつもりのサブクエリが複数行返ってしまう。LIMIT 1 を付けるか集約関数でまとめないと、エラーになるか、想定外の結果になります。
• UPDATE OR REPLACE と UPSERT を混同する。 OR REPLACE は衝突した行を削除します。INSERT ... ON CONFLICT DO UPDATE はその場で更新します。別物です。

次は DELETE

UPDATE は行を書き換える操作、DELETE は行を消す操作です。WHERE を丁寧に書く姿勢は同じですし、「先に SELECT で確認する」という習慣も同じように事故を防いでくれます。次のページで見ていきましょう。