INSERT・UPDATE・DELETE
商品表へ既定値付きで行を追加し、在庫の加減算と条件付き削除を行います。省略した値と明示したNULLの違い、対象が零件の更新、全行に掛かる更新と取消しまで、同じ表の変化を追います。
説明
INSERT・UPDATE・DELETEは、表に保存された行を変更するSQLです。どの列へ値を入れるか、どの行を対象にするかを明示し、変更された値をRETURNINGやSELECTで確認します。
商品表へ既定値付きで行を追加し、在庫の加減算と条件付き削除を行います。省略した値と明示したNULLの違い、対象が零件の更新、全行に掛かる更新と取消しまで、同じ表の変化を追います。
1既定値のある商品表
在庫の既定値は0、販売状態の既定値はdraftとします。
CREATE TABLE products(id INTEGER PRIMARY KEY,name TEXT NOT NULL,stock INTEGER NOT NULL DEFAULT 0 CHECK(stock>=0)既定の在庫0と非負の制約,status TEXT DEFAULT 'draft'省略時はdraft) STRICT;
SELECT sql FROM sqlite_schema保存されている表定義 WHERE name='products';DEFAULTはINSERTで値を省略したときの値です。stockにはNOT NULLと0以上のCHECKも付きます。statusはNULLを許す設計にして、省略とNULLの違いを確認します。
2名前だけを指定して追加する
INSERTの列名をnameだけにします。
INSERT INTO products(name)指定する列はnameだけ VALUES('ノート') RETURNING id,name,stock,status追加された行の値を取得;id 1、在庫0、状態draftが返ります。INTEGER PRIMARY KEYのIDは省略時に生成されます。RETURNINGは追加された行の値を返し、既定値を含めて確認できます。
3明示したNULLは既定値と異なる
在庫を5、statusをNULLとして追加します。
INSERT INTO products(name,stock,status) VALUES('ペン',5,NULL)在庫とNULLを明示 RETURNING *追加行の全列;id 2、在庫5、状態NULLです。DEFAULTがあっても、明示的なNULLを自動でdraftへ変換しません。列の省略と、値がないことの指定は異なります。
4既存の在庫へ加算する
ノートの在庫を現在値から3増やします。
UPDATE products SET stock=stock+3現在値へ3を加える WHERE id=1変更対象をIDで限定 RETURNING id,stock;在庫が0から3へ変わります。右辺のstockは変更前の値です。WHERE id=1で対象をノート一行へ限定しており、ペンの在庫は5のままです。
5在庫が足りる場合だけ減らす
在庫3個から2個を引きます。
UPDATE products SET stock=stock-22個を減らす WHERE id=1 AND stock>=2必要数がある行だけ RETURNING id,stock;在庫は1になります。WHEREへ必要数以上という条件を加えたため、引き算できる行だけが更新対象です。変更と条件判定を同じUPDATEへ書いています。
6同じ出庫をもう一度試す
在庫1個に対して2個の出庫を試します。
UPDATE products SET stock=stock-2 WHERE id=1 AND stock>=2現在の在庫1では不成立 RETURNING id,stock;
SELECT changes()直前の変更文の対象件数 AS changed_rows;RETURNINGの行はなく、changes()は0です。在庫不足でWHEREが成立せず、更新対象がありませんでした。エラーが出ないことだけで更新成功と判断せず、対象件数も確認します。
7条件を省くと全行が対象になる
BEGINで変更を保留し、全商品の状態をactiveへ変えます。
BEGIN変更をまとめる処理を開始;
UPDATE products SET status='active'WHEREなしで全行を変更 RETURNING id,status;二行ともactiveになります。WHEREがなければ表の全行が対象です。BEGINから始めたトランザクション内の変更なので、この接続では変更が見えますが、次にROLLBACKで取り消せます。
8変更を取り消す
ROLLBACKして保存値を表示します。
ROLLBACKBEGIN以降を取り消す;
SELECT id,name,stock,status復元後の全行 FROM products ORDER BY id;ノートは在庫1・draft、ペンは在庫5・NULLへ戻ります。取り消したのはBEGIN以降の状態変更です。それ以前に成功していた追加や出庫は残っています。
9IDを指定して削除する
ペンの行だけを削除します。
DELETE FROM products WHERE id=2削除対象を限定 RETURNING id,name,stock削除前の値を取得;削除されたid 2、ペン、在庫5が返ります。DELETEのRETURNINGは削除前の値です。表の定義は残り、指定した行だけが取り除かれます。
10同じ削除と最終状態
同じIDを再び削除し、残った行を確認します。
DELETE FROM products WHERE id=2;
SELECT changes()今回の削除件数 AS deleted_rows;
SELECT * FROM products残った原表 ORDER BY id;削除件数は0、表にはノート一行が残ります。SQL文が正常に終了しても、条件に合う行がない場合にはデータは変わりません。最後の状態は、追加・加算・出庫・取消し・削除の各操作から説明できます。
まとめ
列を省略したINSERTは既定値を使い、明示したNULLとは異なります。
UPDATEとDELETEはWHEREで対象を決め、変更件数と値を確認します。
RETURNINGと原表の確認を使い分け、ROLLBACKが取り消す範囲を把握します。
操作履歴と解説を振り返れます。
SQLite 3.54.0
CREATE TABLE products(id INTEGER PRIMARY KEY,name TEXT NOT NULL,stock INTEGER NOT NULL DEFAULT 0 CHECK(stock>=0),status TEXT DEFAULT 'draft') STRICT;
SELECT sql FROM sqlite_schema WHERE name='products';╭──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────╮ │ sql │ ╞══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡ │ CREATE TABLE products(id INTEGER PRIMARY KEY,name TEXT NOT NULL,stock INTEGER NOT NULL DEFAULT 0 CHECK(stock>=0),status TEXT DEFAULT 'draft') STRICT │ ╰──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────╯
INSERT INTO products(name) VALUES('ノート') RETURNING id,name,stock,status;╭────┬────────┬───────┬────────╮ │ id │ name │ stock │ status │ ╞════╪════════╪═══════╪════════╡ │ 1 │ ノート │ 0 │ draft │ ╰────┴────────┴───────┴────────╯
INSERT INTO products(name,stock,status) VALUES('ペン',5,NULL) RETURNING *;╭────┬──────┬───────┬────────╮ │ id │ name │ stock │ status │ ╞════╪══════╪═══════╪════════╡ │ 2 │ ペン │ 5 │ NULL │ ╰────┴──────┴───────┴────────╯
UPDATE products SET stock=stock+3 WHERE id=1 RETURNING id,stock;╭────┬───────╮ │ id │ stock │ ╞════╪═══════╡ │ 1 │ 3 │ ╰────┴───────╯
UPDATE products SET stock=stock-2 WHERE id=1 AND stock>=2 RETURNING id,stock;╭────┬───────╮ │ id │ stock │ ╞════╪═══════╡ │ 1 │ 1 │ ╰────┴───────╯
UPDATE products SET stock=stock-2 WHERE id=1 AND stock>=2 RETURNING id,stock;
SELECT changes() AS changed_rows;╭──────────────╮ │ changed_rows │ ╞══════════════╡ │ 0 │ ╰──────────────╯
BEGIN;
UPDATE products SET status='active' RETURNING id,status;╭────┬────────╮ │ id │ status │ ╞════╪════════╡ │ 1 │ active │ │ 2 │ active │ ╰────┴────────╯
ROLLBACK;
SELECT id,name,stock,status FROM products ORDER BY id;╭────┬────────┬───────┬────────╮ │ id │ name │ stock │ status │ ╞════╪════════╪═══════╪════════╡ │ 1 │ ノート │ 1 │ draft │ │ 2 │ ペン │ 5 │ NULL │ ╰────┴────────┴───────┴────────╯
DELETE FROM products WHERE id=2 RETURNING id,name,stock;╭────┬──────┬───────╮ │ id │ name │ stock │ ╞════╪══════╪═══════╡ │ 2 │ ペン │ 5 │ ╰────┴──────┴───────╯
DELETE FROM products WHERE id=2;
SELECT changes() AS deleted_rows;
SELECT * FROM products ORDER BY id;╭──────────────╮ │ deleted_rows │ ╞══════════════╡ │ 0 │ ╰──────────────╯ ╭────┬────────┬───────┬────────╮ │ id │ name │ stock │ status │ ╞════╪════════╪═══════╪════════╡ │ 1 │ ノート │ 1 │ draft │ ╰────┴────────┴───────┴────────╯
演習完了