SQLite 3 DELETE に関するまとめ

ver 3.36.0 時点

概要

DELETE 文の基本的な使い方。

初級

• DELETE 文の基本的な使い方。

中級

• 削除した行の内容を確認する
• SELECT で並び替えた時の順番を指定して削除する。

参考サイト

• SQLite 公式サイト(英語)
  https://www.sqlite.org/index.html
• SQLite Query Language: DELETE(英語)
  公式のDELETE構文に関するドキュメント
  https://www.sqlite.org/lang_delete.html

SQLite 3 deleteした行の内容を確認する (returning)

概要

DELETE文実行時に削除対象となったレコード（行）の内容を確認するSQL。 RETURNING構文を使用する。

以下のような状況で役に立ちそう。

• 大量のレコードを持つテーブルにDELETE文を実行した際、 意図したレコードが削除されたか確認が楽になる
• トランザクション管理の ROLLBACK 機能と組み合わせれば SQLの開発時にテストやデバッグが捗る。

RETURNING構文はSQLite の ver 3.35.0 から利用可能。

構文

DELETE FROM table_name
WHERE
    column_name = value ...
RETURNING
    column_name1
    , column_name2 .... 
;
                    

実行例

環境

• Windows 10 64bit
• SQLite3 (3.35.5) Command-Line Shell

サンプルテーブル(product)

id	name	quantity
1	tomato	100
2	potato	120
3	pumpkin	50

「id=2」のレコードを削除し、削除された行を確認してみる。

sqlite> SELECT * FROM product;
1|tomato|100
2|potato|120
3|pumpkin|50

sqlite> -- # 1.
sqlite> DELETE FROM product
    ...> WHERE
    ...>     id = 2
    ...> RETURNING
    ...>     id
    ...>     , name
    ...>     , quantity
    ...> ;
2|potato|120

sqlite> -- # 2.
sqlite> SELECT * FROM product;
1|tomato|100
3|pumpkin|50
                    

1. RETURNING構文を使うと削除対象になったレコードが表示される
2. テーブルからは正しく削除されている

returningで取得するカラムに*を指定することもできる。

sqlite> DELTE FROM product
    ...> WHERE
    ...>     id = 2
    ...> RETURNING
    ...>     *
    ...> ;
2|potato|120
                    

RETURNING で取得する内容に別名をつけることもできる。

sqlite> .mode box
sqlite> .headers on
sqlite> DELETE FROM product
    ...> WHERE
    ...>     id = 2
    ...> RETURNING
    ...>     id as column1
    ...>     , name as column2
    ...>     , quantity as column3
    ...> ;
┌─────────┬─────────┬─────────┐
│ column1 │ column2 │ column3 │
├─────────┼─────────┼─────────┤
│ 2       │ potato  │ 120     │
└─────────┴─────────┴─────────┘    
                    

SQLite 3 コマンドラインツールでselectの結果を見やすくする
SQLite 3 コマンドラインツールでカラム名を表示する「.headers」について

利用上の制限

SQLite 3.35.5 の時点でRETURNING構文に以下のような制限がある。
これは将来のバージョンで改善される可能性がある。

• 仮想テーブルへの DELETE 文では使用できない。
• 素の DELETE 文でのみ使用可能。トリガー内の DELETE 文では使用できない。
• サブクエリでは使用できない。 例えばDELETE ～ RETURNING ～で取得したレコードを WHERE 句に使って 他のテーブルからデータをSELECTする、といったことはできない。
• RETURNING 文で取得したレコードをソートすることはできない。
• DELETE 文を含むトリガーが設定されているテーブルに対しDELETE ～ RETURNING ～を実行しても、 トリガーが削除した分のレコードは取得できない。
• RETURNING 句で取得したレコードには集計関数や window 関数を使用できない。

参考サイト

• SQLite 公式サイト(英語)
  https://www.sqlite.org/index.html
• SQLite Query Language: DELETE(英語)
  公式のDELETE構文に関するドキュメント
  https://www.sqlite.org/lang_delete.html
• RETURNING
  公式のRETURNINGに関するドキュメント
  https://www.sqlite.org/lang_returning.html

SQLite 3 SELECT 時の順番を指定して削除する

概要

SELECT した時の最初から何行目まで削除したい、といった場合の方法。
サブクエリを利用して順番を指定する。

実行例 (シンプルな例)

サンプルテーブル(product)

id	name	quantity
1	tomato	100
2	potato	120
3	pumpkin	50

上記のようなテーブルで「quantity」の値が一番小さいレコードを削除したいという場合、 以下のようなSQLとなる。 （集計関数の min()を使う方法もあるが 今回はORDER BYを使用する）

ポイントとしては

1. サブクエリでは必ずORDER BYを指定する
2. 出来る限り PRIMARY KEY やUNIQUEなカラムを WHERE 句で指定する
3. WHERE 句で指定するカラムとサブクエリで取得するカラムの数と型を揃える

DELETE FROM product
WHERE
    id = (
        SELECT
            id
        FROM
            product
        ORDER BY
            quantity
        LIMIT 1
    )
;
                    

結果(product)

id	name	quantity
1	tomato	100
2	potato	120

実行例 (範囲を指定する)

何番目～何番目を指定する場合はサブクエリで LIMITとOFFSETを指定すればいい。 何番目だけ、としたい場合はLIMITを1にしてやればいい。

サンプルテーブル(product)

name	quantity	insert_date
tomato	100	2021-04-02
potato	120	2021-04-03
pumpkin	50	2021-04-02
tomato	50	2021-04-02
pumpkin	30	2021-04-01

上記のようなテーブルから「insert_date」「quantity」「name」の順番でソートした結果から 2番目〜4番目に古い（2番目から3件の）レコードを削除する場合、以下のようなSQLとなる。

ポイントとしては

1. サブクエリでは必ずORDER BYを指定する
2. WHERE ～ INを利用する
3. キーになるカラムがない場合、ユニークになる条件を満たすカラムをWHEREに指定する
4. WHEREで指定するカラムとサブクエリで取得するカラムの数と型を揃える

DELETE FROM product
WHERE (
    name
    , quantity
    , insert_date
    ) in (
        SELECT
            name
            , quantity
            , insert_date
        FROM
            product
        ORDER BY
            insert_date
            , quantity
            , name
        LIMIT 3
        OFFSET 1
    )
;
                    

結果(product)

name	quantity	insert_date
potato	120	2021-04-03
pumpkin	30	2021-04-01

サブクエリを使わない方法がある？

公式のドキュメントには、サブクエリを使わずに DELETE 文にそのまま ORDER BY、LIMIT、OFFSET 句をつけて実行する方法が記載されている。
ただしこれはデフォルトでは利用できず、SQLITE_ENABLE_UPDATE_DELETE_LIMITオプションを指定して ソースコードからコンパイルしなおさなければならない。
Optional LIMIT and ORDER BY clauses

参考サイト

• SQLite 公式サイト(英語)
  https://www.sqlite.org/index.html
• SQLite Query Language: DELETE(英語)
  公式のDELETE構文に関するドキュメント
  https://www.sqlite.org/lang_delete.html
• The LIMIT clause
  公式のLIMIT, OFFSETに関するドキュメント
  https://www.sqlite.org/lang_select.html#the_limit_clause

SQLite 3 テーブルのデータを削除する (DELETE)

概要

SQLite3 における DELETE 文の基本的な使い方。

構文

削除したいレコードの条件を WHERE 句で指定するのが基本。

DELETE FROM table_name
WHERE
    column = value ...
                    

実行例

サンプルテーブル(product)

id	name	quantity
1	tomato	100
2	potato	120
3	pumpkin	50

「name」カラムの値が「tomato」のレコードを削除する

DELETE FROM product
WHERE
    name = 'tomato';
                    

結果

id	name	quantity
2	potato	120
3	pumpkin	50

テーブルの全レコードを削除する

SQLite 3 ではOracleやMySQLのようなTRUNCATE構文がサポートされていない。 テーブルの全レコードを削除する場合は WHERE 句を指定せずに DELETE 文を実行すればいい。

TRUNCATEではないからと言って削除処理が遅いわけではない。
WHERE 句や RETURNING 句を指定すると各レコードにアクセスしながら削除していくが、 これらを指定しなかった場合 各レコードにアクセスせずに テーブルの内容全体を削除するよう最適化されている。

• SQLite 3 deleteした行の内容を確認する (returning)

DELETE FROM table_name;
                

参考サイト

• SQLite 公式サイト(英語)
  https://www.sqlite.org/index.html
• SQLite Query Language: DELETE(英語)
  公式のDELETE構文に関するドキュメント
  https://www.sqlite.org/lang_delete.html
• The Truncate Optimization(英語)
  公式のテーブル内容全削除に関するドキュメント
  https://www.sqlite.org/lang_delete.html#the_truncate_optimization