日付の古い順番に削除したいのですが

質問

レコード登録時にテーブルの件数が規定数を越えているならば、指定件数分の既存レコードを削除したいのですが、方法が見つかりません。
目的のテーブルは以下のような構造になっております。

P-Key１：大枠のID（プライマリキー１）
P-Key２：大枠内の序列を表す（プライマリキー２）
THRED：登録元スレッド番号（プライマリキー３）
FILE：ファイルパスを格納
TIMESTAMP：登録日付、DATE型
TERMID：端末ID（プライマリキー４）

この内、削除時に指定するのはスレッド番号と端末IDだけです。
残りのプライマリキーが一つならば、IN句の副問い合わせでできそうな感じですが、２つある為、IN句では指定できませんでした。

以下は、試したSQL文です。ただし、全部失敗しております。
DELETE FROM (select * FROM テーブル WHERE THREAD = 1 AND TERMID = '端末ID' ORDER BY TIMESTAMP) WHERE ROWNUM < 3;

DELETE FROM テーブル WHERE P-Key1 IN (SELECT P-Key1 FROM テーブル WHERE THREAD = 1 AND TERMID = '端末ID' AND ROWNUM < 3 ORDER BY TIMESTAMP) AND P-Key2 IN (SELECT P-Key2 FROM テーブル WHERE THREAD = 1 AND TERMID = '端末ID' AND ROWNUM < 3 ORDER BY TIMESTAMP);

よろしくお願いします。

回答No.1

k_o_r_o_c_h_a_n · ベストアンサー

>残りのプライマリキーが一つならば、IN句の副問い合わせでできそうな感じですが、
>２つある為、IN句では指定できませんでした。

こんな感じで、２項目以上のIN条件(メンバーシップ検査)は可能です。
サンプルがROWNUMを使っているので、同じようにROWNUMを使った場合です。

DELETE FROM テーブル
WHERE
(P-Key1,P-Key2)
IN
(
SELECT P-Key1,P-Key2
FROM
(
SELECT P-Key1,P-Key2,ROWNUM R
FROM
(
SELECT P-Key1,P-Key2 FROM テーブル WHERE THREAD = 1 AND TERMID = '端末ID' ORDER BY TIMESTAMP
)
)
WHERE R<3
)
;

ROWNUMをROW_NUMBER分析関数に書き換えると、サブクエリが少し単純化されます。

DELETE FROM テーブル
WHERE
(P-Key1,P-Key2)
IN
(
SELECT P-Key1,P-Key2
FROM
(
SELECT P-Key1,P-Key2,ROW_NUMBER() OVER(PARTITION BY P-Key1,P-Key2 ORDER BY TIMESTAMP) R
FROM テーブル WHERE THREAD = 1 AND TERMID = '端末ID'
)
WHERE R<3
)
;

お礼

ご回答を下さりまして、ありがとうございます。

ROWNUMを使ったSQL文の方で、期待の結果が得られました。
ただ、ROWNUMBER()関数を使った方では、予めSELECT文で試したところ、2件ではなく該当する全件数が出力されましたので、簡単ながら、この事も報告に添えます。

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

蛇足ながら、以下に検証の為に使用したSQL文を添えます。
SELECT TO_CHAR(TIMESTAMP,'RR-MM-DD HH24:MI:SS') FROM テーブル WHERE (P-Key1,P-Key2) IN ( SELECT P-Key1,P-Key2 FROM ( SELECT P-Key1,P-Key,ROWNUM R FROM ( SELECT P-Key1,P-Key2 FROM テーブル WHERE THREAD = 1 AND TERMID = '端末ID' ORDER BY TIMESTAMP ) ) WHERE R<3 );

DELETE FROM テーブル WHERE (P-Key1,P-Key) IN ( SELECT P-Key1,P-Key2 FROM ( SELECT P-Key1,P-Key2,ROWNUM R FROM ( SELECT P-Key1,P-Key2 FROM テーブル WHERE THREAD = 1 AND TERMID = '端末ID' ORDER BY TIMESTAMP ) ) WHERE R<3 );