[SQL]重複行のうち条件に一致するデータを削除したい

Question

以下のテーブルがあります。(キー梨、一行目が列名、二行目以降がデータ）

id_goods nm_goods price
01 みかん 200
01 みかん 150
01 みかん 100
02 りんご 100
03 チョコ 120

このうち、id_goodsが重複するデータがあった場合priceが最大のもの以外を削除したいのですが
どのようなSQLが正解でしょうか。
以下を試しましたがダメでした

delete t1 from tbl_goods t1 inner join
(select id_goods as obj_id,max(price) as obj_price from tbl_goods
group by id_goods
having count(id_goods) > 1) s1
on t1.id_goods = s1.obj_id
and t1.price <> s1.obj_price

オラクルです。
夜分にすみませんがご教授お願いいたします。

Comments

[退会済みユーザー]

SHOW CREATE TABLE LIKE 'tbl_goods' で得られるテーブル定義を追記してください。実際に実行して動作確認したものを回答できますので。

[999turbulence]

ありがとうございます。

ご教示いただいたSQLが通りませんでした。
自宅でSQLの学習用に使用している
Oracle Application Expressというものの中のSQLワークショップです。

DDLは
create table tbl_goods
(id_goods char(1)
,nm_goods varchar2(60)
,price number(6,0)
)
です。

[退会済みユーザー]

あら、Oracleなんですね。こちらに環境がないので、動作確認できませんね。

[999turbulence]

すみません。id_goods char(2)です。

[退会済みユーザー]

タグに Oracle を追加しておきましょう。

Answer 1

相関副問合わせを用いてみてはどうでしょう？

DELETE 
FROM
    tbl_goods t1 
WHERE
    EXISTS ( 
        SELECT 
            * 
        FROM
            tbl_goods t2 
        WHERE
            t1.id_goods = t2.id_goods
        AND t1.price < t2.price
    ); 

上記クエリでPostgreSQLだと動作します。
最大金額が複数ある場合は最大金額のレコードは複数件残ったままになりますがね。

Comments

[999turbulence]

ありがとうございます。
実行したところ期待した結果になりました。

Answer 2

未検証ですがこんな感じではどうでしょうか。

delete from tbl_goods
  where (id_goods,price) not in (select id_goods, max(price) from tbl_goods group by id_goods);

Comments

[999turbulence]

ありがとうございます。
実行したところ期待した結果になりました。
シンプルで分かりやすいです。

Answer 3

自分だったら…

1. 退避用テーブルを作成
2. id_goods,nm_goods,price(max) を退避用テーブルにコピー
3. 元テーブルを TRUNCATE
4. 別テーブルのデータを元テーブルにコピー
5. 退避用テーブルを削除

Comments

[takasima20]

ご存じかもしれませんが一時テーブルを作るのはこんな感じで。
CREATE TABLE tmp_table NOLOGGING PARALLEL
AS SELECT * FROM src_table WHERE 1<>1;

[999turbulence]

ありがとうございます。
解決いたしました。

一時テーブルを使ったやり方も試してみます。

Answer 4

Shift the Oracle|DELETE

ビューを使った削除（インラインビューを使った削除）が特に参考になるのではないかと。

Comments

[999turbulence]

ありがとうございます。
解決いたしました。