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番目に古いレコードを削除する場合、以下のような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