SQL・データベース
PR

SQLのINSERT・UPDATE・DELETEとトランザクション入門|実行結果で解説

SQLのINSERT・UPDATE・DELETEとトランザクション入門|実行結果で解説
hr
記事内に商品プロモーションを含む場合があります

SQL でデータを「追加する・変更する・削除する」のが INSERT・UPDATE・DELETE です。この記事では、練習用の商品テーブルで実際に実行した結果と一緒に、基本の書き方、WHERE を付け忘れたときの事故と防ぎ方、変更をまとめて確定・取り消しするトランザクション(BEGIN・COMMIT・ROLLBACK)を解説します。

制約違反のエラー、UPSERT、TRUNCATE と DELETE の違い、Python の sqlite3 での書き方も紹介します。SELECT の基本は「SQL入門:SELECT文の基本」をご覧ください。

INSERT・UPDATE・DELETE とは

3 つともテーブルの行を書き換える命令です。SELECT と合わせて DML(データ操作言語)と呼ばれます。

命令すること基本の形
INSERT行を追加するINSERT INTO テーブル (列, …) VALUES (値, …);
UPDATE行の値を変更するUPDATE テーブル SET 列 = 値 WHERE 条件;
DELETE行を削除するDELETE FROM テーブル WHERE 条件;

UPDATE と DELETE は、WHERE を省略するとテーブルのすべての行が対象になります。SELECT なら全件が表示されるだけですが、UPDATE・DELETE では全件が書き換わります。この記事でいちばん覚えてほしいポイントです。

練習用のテーブルを用意する

Python に標準で入っている SQLite(エスキューライト)を使います。次のコードを sql_dml_practice.py という名前で保存してください。商品(products)・仕入れ候補(new_items)・注文(orders)のテーブルをメモリー上に作ります。データは終了すると消えるので、何度でもやり直せます。

import sqlite3

con = sqlite3.connect(":memory:", isolation_level=None)
con.executescript("""
CREATE TABLE products (
  id    INTEGER PRIMARY KEY,
  code  TEXT    NOT NULL UNIQUE,
  name  TEXT    NOT NULL,
  price INTEGER NOT NULL,
  stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0)
);
INSERT INTO products VALUES
  (1, 'A-001', 'ノートPC A',      98000,  5),
  (2, 'A-002', 'ノートPC B',     128000,  2),
  (3, 'B-001', 'マウス',           2980, 30),
  (4, 'B-002', 'キーボード',       6480, 12),
  (5, 'C-001', 'USBメモリ 32GB',   1280,  0);

CREATE TABLE new_items (
  code     TEXT    NOT NULL,
  name     TEXT    NOT NULL,
  price    INTEGER NOT NULL,
  approved INTEGER NOT NULL
);
INSERT INTO new_items VALUES
  ('C-002', '外付けSSD 1TB', 12800, 1),
  ('C-003', 'SDカード 64GB',  1980, 1),
  ('D-001', 'モニター 24型', 19800, 0);

CREATE TABLE orders (
  id         INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL,
  qty        INTEGER NOT NULL
);
""")

print("SQL を入力して Enter(終了は exit)")
while True:
    mark = "(トランザクション中)" if con.in_transaction else ""
    sql = input(f"sql{mark}> ").strip()
    if sql.lower() in ("exit", "quit"):
        break
    if not sql:
        continue
    try:
        cur = con.execute(sql)
    except sqlite3.Error as e:
        print("エラー:", e)
        continue
    if cur.description is None:
        if cur.rowcount >= 0:
            print(f"({cur.rowcount} 行を処理)")
        else:
            print("OK")
        continue
    print(" | ".join(d[0] for d in cur.description))
    rows = cur.fetchall()
    for row in rows:
        print(" | ".join("NULL" if v is None else str(v) for v in row))
    print(f"({len(rows)} 行)")

保存したフォルダーで次のコマンドを実行すると「sql>」と表示されるので、この記事の SQL を上から順に 1 行ずつ入力して Enter を押します(Python 3.12・SQLite 3.45 で確認しました)。INSERT などを実行すると、処理した行数が表示されます。

py sql_dml_practice.py

isolation_level=None は、自分で書いた BEGIN・COMMIT のとおりにトランザクションを動かすための設定です(後の章で説明します)。トランザクションの途中では、入力欄が「sql(トランザクション中)>」に変わります。商品テーブルの code は商品コード、stock は在庫数で、最初は 5 行です。

INSERT:行を追加する

列を指定して 1 行追加する

テーブル名の後ろのかっこに列名を、VALUES の後ろに同じ順番で値を書きます。

INSERT INTO products (code, name, price) VALUES ('B-003', 'USBケーブル', 980);

「(1 行を処理)」と表示されます。SELECT * FROM products WHERE code = 'B-003'; で確かめると、次の行が増えています。

id | code | name | price | stock
6 | B-003 | USBケーブル | 980 | 0
(1 行)

書かなかった列には自動で値が入りました。id は INTEGER PRIMARY KEY なので次の番号の 6、stock は DEFAULT 0(既定値)なので 0 です。

列名を省略して INSERT INTO products VALUES ('B-004', 'Webカメラ', 4980, 8); と書くと、「エラー: table products has 5 columns but 4 values were supplied」(列は 5 つなのに値が 4 つ)になりました。列名を省略すると、テーブルのすべての列に、定義した順番どおりに値を書く必要があり、あとで列が増えたときにも動かなくなります。列名は省略せずに書きましょう。

複数の行をまとめて追加する

VALUES の後ろに、かっこをカンマで区切って並べます。

INSERT INTO products (code, name, price, stock) VALUES ('B-004', 'Webカメラ', 4980, 8), ('B-005', 'ヘッドセット', 7980, 6);

「(2 行を処理)」と表示され、id 7 と 8 の 2 行が追加されます。この書き方は MySQL・PostgreSQL の公式ドキュメントの例にも出てきます。

INSERT … SELECT:別のテーブルの行を追加する

VALUES の代わりに SELECT を書くと、その結果の行をまとめて追加できます。仕入れ候補(new_items)のうち、承認済み(approved が 1)の 2 件だけを商品テーブルに入れてみます。

INSERT INTO products (code, name, price) SELECT code, name, price FROM new_items WHERE approved = 1;

「(2 行を処理)」と表示されます。SELECT * FROM products WHERE id >= 6; の結果は次のとおりで、承認されていないモニター(D-001)は入っていません。

id | code | name | price | stock
6 | B-003 | USBケーブル | 980 | 0
7 | B-004 | Webカメラ | 4980 | 8
8 | B-005 | ヘッドセット | 7980 | 6
9 | C-002 | 外付けSSD 1TB | 12800 | 0
10 | C-003 | SDカード 64GB | 1980 | 0
(5 行)

UPDATE:行の値を変更する

SET の後ろに「列 = 新しい値」を、WHERE の後ろに変更する行の条件を書きます。マウス(B-001)を 2,480 円に値下げします。

UPDATE products SET price = 2480 WHERE code = 'B-001';

「(1 行を処理)」と表示されます。複数の列はカンマで区切って変更でき、今の値を使った計算もできます。外付けSSD と SDカードを 500 円値下げし、在庫を 10 個増やして、SELECT で確かめた結果です。

UPDATE products SET price = price - 500, stock = stock + 10 WHERE code IN ('C-002', 'C-003');
code | name | price | stock
C-002 | 外付けSSD 1TB | 12300 | 10
C-003 | SDカード 64GB | 1480 | 10
(2 行)

なお、存在しない商品コードを指定した UPDATE products SET price = 100 WHERE code = 'Z-999'; は、エラーにならず「(0 行を処理)」になりました。条件に合う行がなくても UPDATE・DELETE は成功扱いです。プログラムから実行するときは、処理した行数(Python なら cursor.rowcount)を確かめると、「更新したつもりで何も変わっていない」ことに気づけます。

DELETE:行を削除する

在庫が 0 の商品を削除します。削除する前に、同じ WHERE で SELECT して、対象の行を確かめます。

SELECT id, code, name, stock FROM products WHERE stock = 0;
id | code | name | stock
5 | C-001 | USBメモリ 32GB | 0
6 | B-003 | USBケーブル | 0
(2 行)

消してよい 2 行だと確認できたら、SELECT 〜 FROM の部分を DELETE FROM に書き換えて実行します。

DELETE FROM products WHERE stock = 0;

「(2 行を処理)」と表示され、SELECT の件数と一致しました。

WHERE の付け忘れを再現する

WHERE を付け忘れるとどうなるかを、あとで元に戻せるようにトランザクション(次の章で説明します)の中で試します。まず BEGIN を実行します。

BEGIN;

入力欄が「sql(トランザクション中)>」に変わったら、マウスだけ値段を変えるつもりで、WHERE を書き忘れた UPDATE を実行します。

UPDATE products SET price = 0;

「(8 行を処理)」と表示されました。SELECT code, name, price FROM products; で確かめると、全商品が 0 円です。

code | name | price
A-001 | ノートPC A | 0
A-002 | ノートPC B | 0
B-001 | マウス | 0
B-002 | キーボード | 0
B-004 | Webカメラ | 0
B-005 | ヘッドセット | 0
C-002 | 外付けSSD 1TB | 0
C-003 | SDカード 64GB | 0
(8 行)

ここで ROLLBACK; を実行すると、BEGIN 以降の変更がすべて取り消され、もう一度 SELECT すると値段は元(ノートPC A は 98000、マウスは 2480 など)に戻っていました。BEGIN をせずに実行した UPDATE・DELETE は、その場で確定する(次の章で説明する自動コミット)ので ROLLBACK では戻せません。バックアップから戻すしかなく、本番では大きな事故になります。DELETE FROM products; なら全行が消えます。

事故を防ぐために、次の習慣をつけましょう。

  1. 先に同じ WHERE で SELECT し、対象の行と件数を確かめる
  2. 本番のデータを手で変更するときは BEGIN してから実行する
  3. 表示された処理件数が SELECT の件数と同じなら COMMIT、違えば ROLLBACK する
安全に変更する手順の流れ。同じ WHERE で SELECT して対象を確認し、BEGIN、UPDATE または DELETE を実行して処理件数を SELECT の件数と比べ、同じなら COMMIT、違えば ROLLBACK する。WHERE を忘れた UPDATE で 8 行を処理したときは ROLLBACK で元に戻った
図 1:UPDATE・DELETE を安全に実行する手順

長い SQL は SQL 整形ツールで改行を入れて整形すると、WHERE があるかどうかがひと目で分かります。

トランザクション:まとめて確定・取り消す

トランザクションは、複数の変更を「全部成功」か「全部なかったこと」のどちらかにする仕組みです。注文を受けたら「注文を 1 行追加」と「在庫を減らす」を行いますが、片方だけ成功すると食い違いが起きます。

命令意味
BEGINトランザクションを始める
COMMITBEGIN 以降の変更を確定する(ほかの接続からも見えるようになる)
ROLLBACKBEGIN 以降の変更をすべて取り消す

BEGIN を書かないときは、SQLite も PostgreSQL も 1 つの文ごとに自動でトランザクションを始めて、成功したら確定します(両方の公式ドキュメントで確認)。この動きを自動コミット(autocommit)と呼びます。

成功したら COMMIT する

ノートPC A(id 1)を 2 台注文する処理を、次の 4 つの SQL で 1 行ずつ実行します。

BEGIN;
INSERT INTO orders (product_id, qty) VALUES (1, 2);
UPDATE products SET stock = stock - 2 WHERE id = 1;
COMMIT;

INSERT と UPDATE でそれぞれ「(1 行を処理)」と表示され、COMMIT で確定します。注文テーブルに 1 行(id 1)が入り、ノートPC A の在庫は 5 から 3 になりました。

途中でエラーが起きたら ROLLBACK で戻す

次は在庫が 2 台しかないノートPC B(id 2)を 3 台注文します。在庫の列には「0 以上」という CHECK 制約があるので、在庫を減らす UPDATE が失敗するはずです。

BEGIN;
INSERT INTO orders (product_id, qty) VALUES (2, 3);
UPDATE products SET stock = stock - 3 WHERE id = 2;
sql(トランザクション中)> (1 行を処理)
sql(トランザクション中)> エラー: CHECK constraint failed: stock >= 0

UPDATE はエラーになりましたが、入力欄は「トランザクション中」のままです。SELECT * FROM orders; を実行すると、在庫が足りなかった注文(id 2)が残っています。

id | product_id | qty
1 | 1 | 2
2 | 2 | 3
(2 行)

SQLite の公式ドキュメントによると、制約違反が起きたときの標準の動き(ABORT)は「その文の変更だけを取り消し、同じトランザクションの前の文の変更は残して、トランザクションを続ける」です。つまりエラーが起きても、トランザクション全体が自動で取り消されるわけではありません。ここで COMMIT すると、在庫を減らさないまま注文だけが確定してしまいます。

エラーが起きたら ROLLBACK; を実行します。もう一度 SELECT * FROM orders; すると、注文は id 1 の 1 行だけに戻りました。

成功の例ではノートPC A の注文を BEGIN、INSERT、UPDATE(在庫 5 から 3)、COMMIT で両方確定する。失敗の例ではノートPC B の注文の INSERT のあと UPDATE が CHECK 制約のエラーになり、ROLLBACK で注文 id 2 も取り消す
図 2:トランザクションの COMMIT と ROLLBACK

エラー後の動きはデータベースによって違い、PostgreSQL ではトランザクションが「中断された状態」になります(公式チュートリアル)。どのデータベースでも、エラーが起きたら ROLLBACK すると覚えておけば安全です。

Python の sqlite3 での書き方(with と autocommit)

プログラムからは、エラーが起きたら自動で ROLLBACK する書き方にします。Python の sqlite3 では、接続(Connection)を with で使うと、ブロックが例外なく終われば COMMIT、例外が起きれば ROLLBACK されます。

import sqlite3

con = sqlite3.connect(":memory:", autocommit=False)
con.execute("CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL,"
            " stock INTEGER NOT NULL CHECK (stock >= 0))")
con.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY,"
            " product_id INTEGER NOT NULL, qty INTEGER NOT NULL)")
con.execute("INSERT INTO products VALUES (1, 'ノートPC A', 5), (2, 'ノートPC B', 2)")
con.commit()


def place_order(product_id, qty):
    try:
        with con:  # 成功したら COMMIT、例外が起きたら ROLLBACK
            con.execute("INSERT INTO orders (product_id, qty) VALUES (?, ?)",
                        (product_id, qty))
            con.execute("UPDATE products SET stock = stock - ? WHERE id = ?",
                        (qty, product_id))
        print(f"注文しました:商品 {product_id} を {qty} 個")
    except sqlite3.IntegrityError as e:
        print(f"注文できません:{e}")


place_order(1, 2)
place_order(2, 3)
print("orders:", con.execute("SELECT * FROM orders").fetchall())
print("stock:", con.execute("SELECT id, stock FROM products").fetchall())
con.close()

order_tx.py という名前で保存して py order_tx.py で実行すると、次のように表示されました。2 回目の注文は、追加した注文の行ごと取り消されています。

注文しました:商品 1 を 2 個
注文できません:CHECK constraint failed: stock >= 0
orders: [(1, 1, 2)]
stock: [(1, 3), (2, 2)]
  • autocommit=False:Python 3.12 で追加された設定(connect() の引数と Connection.autocommit 属性)で、公式ドキュメントで推奨されている値です。常にトランザクションが開いた状態になり、commit() と rollback() で確定・取り消しします。3.11 以前では使えません
  • 何も指定しないとき:autocommit は LEGACY_TRANSACTION_CONTROL(3.12 より前と同じ動き)になり、INSERT・UPDATE・DELETE の前に自動で BEGIN されます。commit() を呼ぶまで確定しません。公式ドキュメントには、将来この既定値が False に変わると書かれています
  • with con: は接続を閉じません。閉じるには con.close() を呼びます
  • SQL に値を入れるときは、文字列をつなげず ?(プレースホルダ)を使います。理由は「SQLインジェクションとは?仕組みとプレースホルダによる対策」で詳しく解説しています

制約違反のエラーを読む

制約(テーブルに入れてよい値のルール)に違反する INSERT・UPDATE はエラーになり、行は追加・変更されません。練習用のテーブルで実際に出たエラーです。

制約実行した SQLエラーメッセージ
NOT NULLINSERT INTO products (code, price) VALUES ('E-001', 500);NOT NULL constraint failed: products.name
UNIQUEINSERT INTO products (code, name, price) VALUES ('B-001', 'マウス', 2480);UNIQUE constraint failed: products.code
PRIMARY KEYINSERT INTO products (id, code, name, price) VALUES (1, 'E-001', 'テスト', 500);UNIQUE constraint failed: products.id
CHECKUPDATE products SET stock = -1 WHERE id = 3;CHECK constraint failed: stock >= 0

NOT NULL は列を書かなかったときも、NULL を書いたときも同じエラーでした。主キー(行を見分ける番号)の重複は、SQLite では UNIQUE と同じ文言です。最後の「テーブル名.列名」を見れば、どの列が原因かが分かります。Python では、これらは sqlite3.IntegrityError という例外になります。

UPSERT:あれば更新、なければ追加

「同じ商品コードがなければ追加、あれば更新」を 1 文で書くのが UPSERT です。標準 SQL ではないので、データベースごとに書き方が違います。

SQLite・PostgreSQL:ON CONFLICT

INSERT INTO products (code, name, price, stock) VALUES ('B-001', 'マウス', 2280, 5) ON CONFLICT (code) DO UPDATE SET price = excluded.price, stock = stock + excluded.stock;

B-001 はすでにあるので、追加の代わりに更新され、SELECT * FROM products WHERE code = 'B-001'; の結果は「3 | B-001 | マウス | 2280 | 35」になりました。excluded.列名 は「追加しようとした値」、何も付けない stock は「今の値」です。在庫は 30 + 5 で 35 になっています。

DO UPDATE の代わりに DO NOTHING と書くと、重複したときは何もしません(「(0 行を処理)」になりました)。この構文は SQLite 3.24.0(2018 年)で追加され、PostgreSQL の構文にならったものです(SQLite 公式ドキュメント)。PostgreSQL でも同じ形で書け、DO UPDATE ではかっこの列(どの重複を対象にするか)の指定が必須です。この記事では PostgreSQL の実機での実行は確認していません。

MySQL:ON DUPLICATE KEY UPDATE

INSERT INTO products (code, name, price, stock) VALUES ('B-001', 'マウス', 2280, 5) AS new ON DUPLICATE KEY UPDATE price = new.price, stock = stock + new.stock;

MySQL 8.4 の公式マニュアルによると、UNIQUE または PRIMARY KEY が重複したら既存の行を UPDATE します。AS new は追加しようとした行に付ける別名で、古い書き方の VALUES(price) は非推奨とされています。処理件数は、追加なら 1、更新なら 2 です。この SQL は MySQL の実機では確認していません。

TRUNCATE と DELETE の違い

テーブルの全行を消す命令として、MySQL や PostgreSQL には TRUNCATE(トランケート)もあります。公式ドキュメントで確認した違いをまとめます。

DELETE(WHERE なし)TRUNCATE(MySQL 8.4)TRUNCATE(PostgreSQL)
ROLLBACK で戻せるかトランザクション中なら戻せる戻せない(暗黙に COMMIT される)トランザクション中なら戻せる
連番(自動採番)リセットしない最初の値に戻る既定では戻らない(RESTART IDENTITY で戻す)
ON DELETE トリガー動く動かない動かない
速さ1 行ずつ削除(SQLite には後述の最適化あり)テーブルを作り直すので速いテーブルを読まないので速い

MySQL の TRUNCATE は DML ではなく DDL(テーブルの定義を扱う命令)に分類されています。どちらのデータベースでも、ほかのテーブルから外部キーで参照されているテーブルは、既定では TRUNCATE できません。

SQLite には TRUNCATE がありません。SQL 構文の一覧にも載っておらず、TRUNCATE TABLE orders; を実行すると「エラー: near “TRUNCATE”: syntax error」になりました。代わりに DELETE FROM orders; を使います。公式ドキュメントによると、WHERE と RETURNING がなく、トリガーもないテーブルの DELETE は、中身をまとめて消す「truncate 最適化」で速く処理されます。

よくある間違い

INSERT OR REPLACE で、ほかの列の値が消える

SQLite の INSERT OR REPLACE を UPSERT の代わりに使うと、思わぬ結果になります。キーボード(id 4、在庫 12)の値段だけを変えるつもりで実行してみます。

INSERT OR REPLACE INTO products (code, name, price) VALUES ('B-002', 'キーボード', 5980);
id | code | name | price | stock
11 | B-002 | キーボード | 5980 | 0
(1 行)

id が 4 から 11 に変わり、在庫が 12 から 0 になりました。REPLACE は重複した行を削除してから新しい行を追加するため、書かなかった列は既定値になり、id も新しく振られます。ほかのテーブルが id 4 を参照していたら、つながりが切れてしまいます。値を一部だけ変えたいときは ON CONFLICT … DO UPDATE を使いましょう。

INSERT OR REPLACE の前後。実行前は id 4・B-002・キーボード・6480・在庫 12。重複した行を削除してから新しい行を追加するので、実行後は id 11・価格 5980・在庫 0 になる
図 3:INSERT OR REPLACE で id と在庫が変わる仕組み

Python で commit() を忘れて保存されない

autocommit を指定しない(既定の)接続で、ファイルのデータベースに INSERT して、commit() を呼ばずに close() しました。

import sqlite3

con = sqlite3.connect("memo.db")
con.execute("CREATE TABLE IF NOT EXISTS memo (body TEXT)")
con.execute("INSERT INTO memo VALUES ('テスト')")
print("autocommit:", con.autocommit, "/ in_transaction:", con.in_transaction)
con.close()  # commit() を呼ばずに閉じる

con = sqlite3.connect("memo.db")
print("件数:", con.execute("SELECT COUNT(*) FROM memo").fetchone()[0])
con.close()
autocommit: -1 / in_transaction: True
件数: 0

-1 は LEGACY_TRANSACTION_CONTROL の値です。INSERT の前に自動で BEGIN されたまま閉じたので、行は保存されず、エラーも出ません。公式ドキュメントにも「閉じる前に commit() を呼ぶこと」とあります。with con: を使えば、COMMIT の書き忘れを防げます。

よくある質問

BEGIN の中で、もう一度 BEGIN できますか?

SQLite では、トランザクション中に BEGIN すると「cannot start a transaction within a transaction」というエラーになりました。BEGIN していないときに COMMIT すると「cannot commit – no transaction is active」です。トランザクションの中で一部だけを取り消したいときは、SAVEPOINT(セーブポイント)と ROLLBACK TO を使います。

まとめ

  • INSERT は列名を書いて追加する。複数行や INSERT … SELECT でまとめて追加できる
  • UPDATE・DELETE は WHERE がないと全行が対象。先に同じ WHERE で SELECT し、件数を確かめる
  • まとめて成功させたい変更は BEGIN〜COMMIT で囲み、エラーが起きたら ROLLBACK する
  • UPSERT は ON CONFLICT(SQLite・PostgreSQL)や ON DUPLICATE KEY UPDATE(MySQL)で書く。SQLite に TRUNCATE はない

関連する記事・ツール

ABOUT ME
hr
hr
エンジニアとして働いている hr です。仕事や資格勉強で「こんなツールがあれば」と感じたものを、無料Webツールと解説記事にして発信しています。
記事URLをコピーしました