DELETE文を使用することで、テーブルの指定した行を削除することができます。
難しいこともなく使っていたのですが、SELECTした行を残してその他を削除する時ってどうするのだとうと少し困ったので備忘録です。
動作環境
SQLiteonlineとSQL FIDDLEでMySQL5.6、PostgreSQL9.6、SQLiteで動作確認しました。
下記文章は主にPostgreSQLで仕様で調べています。
Delete文でSELECTした行以外を削除する方法
構文
Delete文でSELECTした行以外を削除する書き方は以下のようになります。
DELETE FROM テーブル名 WHERE 条件 not in (Select……)
反対にSELECTした行を消す場合は「where 条件 in」で削除することができます。
SELECTで取得した結果はサブクエリで使用します。
実際にDelete文でSELECTした行以外を削除してみよう！
それでは実際にSELECT文で取得した行以外を削除してみましょう。
使用するテーブル情報
以下のテーブルがあるとします。
| ItemID | ItemName | Quantity | ArriveDate | AREA |
|---|---|---|---|---|
| 1 | ゼリー | 30 | 2020-04-20 | 東京 |
| 2 | プリン | 25 | 2020-04-20 | 大阪 |
| 3 | ゼリー | 30 | 2020-04-23 | 東京 |
| 4 | ティラミス | 15 | 2020-04-22 | 大阪 |
| 5 | ヨーグルト | 30 | 2020-04-20 | 東京 |
| 6 | ゼリー | 20 | 2020-04-22 | 大阪 |
以下練習用テーブル作成コードです。
— CREATE文
CREATE TABLE item (
ItemID numeric(8, 0) unique not null,
ItemName character varying (20) not null,
Quantity numeric(8, 0),
ArriveDate DATE,
AREA character varying (20),
primary key(ItemID)
);
— INSERT文
insert into item
values(0001, ‘ゼリー’, 30, ‘2020-04-20’, ‘東京’),
(0002, ‘プリン’, 25, ‘2020-04-20’, ‘大阪’),
(0003, ‘ゼリー’, 30, ‘2020-04-23’, ‘東京’),
(0004, ‘ティラミス’, 15, ‘2020-04-22’, ‘大阪’),
(0005, ‘ヨーグルト’, 30, ‘2020-04-20’, ‘東京’),
(0006, ‘ゼリー’, 20, ‘2020-04-22’, ‘大阪’);
Delete文でSELECTした行以外を削除するSQL
ItemNameがゼリーのものをSELECTしてそれ以外を削除します。
DELETE FROM item
WHERE
itemName
not in (
SELECT
ItemName
from
item
where
ItemName = ‘ゼリー’
);
■ 削除後のテーブル
| ItemID | ItemName | Quantity | ArriveDate | AREA |
|---|---|---|---|---|
| 1 | ゼリー | 30 | 2020-04-20 | 東京 |
| 3 | ゼリー | 30 | 2020-04-23 | 東京 |
| 6 | ゼリー | 20 | 2020-04-22 | 大阪 |
「subquery has too many columns」とエラーが出てしまうことがあります。その時は not in の前のカラムよりSELECTしているカラムが多いと発生します。以下でさらに複雑な削除方法を書いてみます。
Delete文でSELECTした行以外を削除するSQL(ちょい複雑)
複数条件のあるSELECT文がありそれ以外を削除したい場合についてです。他に書き方があるかもしれませんが知らないので以下のようにしています。
AREAが東京でquantityが21以上のデータをSELECTしそれ以外を削除する。
DELETE FROM item
WHERE
Itemid
not in (
SELECT
itemid
from
( SELECT
Itemid
,quantity
,Area
from
item
where
quantity > 20
and
AREA = ‘東京’
)test
);
■ 削除後のテーブル
| ItemID | ItemName | Quantity | ArriveDate | AREA |
|---|---|---|---|---|
| 1 | ゼリー | 30 | 2020-04-20 | 東京 |
| 3 | ゼリー | 30 | 2020-04-23 | 東京 |
| 5 | ヨーグルト | 30 | 2020-04-20 | 東京 |
サブクエリがちょっと多くて複雑に見えますが、SELECT文を作ったあとにDELETE文を作成していけばさほど難しくないと思います。
