Delete文でSELECTした行以外を削除する方法!SQL初心者の勉強

2020-06-25

DELETE文を使用することで、テーブルの指定した行を削除することができます。
難しいこともなく使っていたのですが、SELECTした行を残してその他を削除する時ってどうするのだとうと少し困ったので備忘録です。

動作環境

SQLiteonlineSQL FIDDLEでMySQL5.6、PostgreSQL9.6、SQLiteで動作確認しました。
下記文章は主にPostgreSQLで仕様で調べています。

Delete文でSELECTした行以外を削除する方法

構文

Delete文でSELECTした行以外を削除する書き方は以下のようになります。

DELETE FROM テーブル名 WHERE 条件 not in (Select......)

反対にSELECTした行を消す場合は「where 条件 in」で削除することができます。
SELECTで取得した結果はサブクエリで使用します。

実際にDelete文でSELECTした行以外を削除してみよう!

それでは実際にSELECT文で取得した行以外を削除してみましょう。

使用するテーブル情報

以下のテーブルがあるとします。

ItemIDItemNameQuantityArriveDateAREA
1ゼリー302020-04-20東京
2プリン252020-04-20大阪
3ゼリー302020-04-23東京
4ティラミス152020-04-22大阪
5ヨーグルト302020-04-20東京
6ゼリー202020-04-22大阪
item TABLE

以下練習用テーブル作成コードです。

-- 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 = 'ゼリー'
);

■ 削除後のテーブル

ItemIDItemNameQuantityArriveDateAREA
1ゼリー302020-04-20東京
3ゼリー302020-04-23東京
6ゼリー202020-04-22大阪
itemテーブル 削除後

「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
);

■ 削除後のテーブル

ItemIDItemNameQuantityArriveDateAREA
1ゼリー302020-04-20東京
3ゼリー302020-04-23東京
5ヨーグルト302020-04-20東京

サブクエリがちょっと多くて複雑に見えますが、SELECT文を作ったあとにDELETE文を作成していけばさほど難しくないと思います。