ver 3.36.0 時点
delete文の基本的な使い方。
delete文実行時に削除対象となったレコード(行)の内容を確認するSQL。 「returning」構文を使用する。
以下のような状況で役に立ちそう。
「returning」構文はSQLite の ver 3.35.0 から利用可能。
delete from table_name
where
column_name = value ...
returning
column_name1
, column_name2 ....
;
| 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
returningで取得するカラムに「*」を指定することもできる。
sqlite> delete 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」構文に以下のような制限がある。
これは将来のバージョンで改善される可能性がある。
select した時の最初から何行目まで削除したい、といった場合の方法。
サブクエリを利用して順番を指定する。
| id | name | quantity |
|---|---|---|
| 1 | tomato | 100 |
| 2 | potato | 120 |
| 3 | pumpkin | 50 |
上記のようなテーブルで「quantity」の値が一番小さいレコードを削除したいという場合、
以下のようなSQLとなる。(集計関数のMIN()を使う方法もあるが今回はorder byを使用する)
ポイントとしては
delete from product
where
id = (
select
id
from
product
order by
quantity
limit 1
)
;
| id | name | quantity |
|---|---|---|
| 1 | tomato | 100 |
| 2 | potato | 120 |
何番目～何番目を指定する場合はサブクエリで「limit」と「offset」を指定すればいい。 何番目だけ、としたい場合はlimitを1にしてやればいい。
| 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となる。
ポイントとしては
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
)
;
| 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
SQLite3におけるdelete文の基本的な使い方。
削除したいレコードの条件をwhere句で指定するのが基本。
delete from table_name
where
column = value ...
| 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句を指定すると各レコードにアクセスしながら削除していくが、
これらを指定しなかった場合 各レコードにアクセスせずに
テーブルの内容全体を削除するよう最適化されている。
delete from table_name;