SELECT文でINを使わずに検索したい

DBはoracle10gです。
テーブル(A_TBL)は以下の4つのカラムで構成されています。
seq_no(連番),key_1(個人番号),key_2(更新日),status(状態)
key_1が同一のレコードは、
最新のレコード(最新とは更新日の大きいレコード)以外のstatusを'0'から'4'にします。
例として実行前と実行後のテーブルは以下のような状態です。

【実行前】
seq_no,key_1,key_2,status
1,001,20080101,1
2,001,20080102,0
3,002,20080101,0
4,003,20080101,0
5,003,20080102,0
6,003,20080103,0
7,004,20080101,0
8,004,20080102,2

【実行後】
seq_no,key_1,key_2,status
1,001,20080101,1
2,001,20080102,0
3,002,20080101,0
4,003,20080101,4
5,003,20080102,4
6,003,20080103,0
7,004,20080101,4
8,004,20080102,2

以下のようなSQLを作成し、更新しようとしました。

update A_TBL
set status = '4'
where status = '0'
and seq_no not in (
select wk2.seq_no
from A_TBL wk2,
(select max(key_2) as key_2,
key_1 as key_1
from A_TBL
group by key_1) wk1
where wk2.key_1 = wk1.key_1
and wk2.key_2 = wk1.key_2
and wk2.status = '0'
)

このSQLはin句を使っていますが、
in句を使わずに同様の更新を実現することは出来るでしょうか？

No.3 ベストアンサー 回答者： dda167 回答日時： 2009/12/22 07:44

昨日（No.2）は簡単に済ませて失礼しました。

眠かったもので（笑）。

> in句でもカンマ区切りで指定した場合は1000件までで、
> 副問合せであれば問題なしという記述もありました。

こちらが正解でしょう。1000件というのは「式のリスト」の制限ですね。
下はマニュアルの抜粋です。

> カンマで区切られた式のリストには、最大1000個の式を指定できます。
> カンマで区切られた式の集合のリストには、任意数の式の集合を含める
> ことができますが、各集合に指定できる式は最大1000個です。

http://otndnld.oracle.co.jp/document/products/or …

SELECT文で指定してやる分には問題ないと思いますよ。

【実験】

SQL> select count(*)
2 from (
3 select level
4 from dual
5 connect by level <= 2000
6 );

COUNT(*)
--------
2000

1行が選択されました。

SQL> select ename, sal
2 from emp
3 where sal in (
4 select level
5 from dual
6 connect by level <= 2000
7 );

ENAME SAL
---------- --------
SMITH 800
ALLEN 1600
WARD 1250
MARTIN 1250
TURNER 1500
ADAMS 1100
JAMES 950
MILLER 1300

8行が選択されました。

# inの使用に問題がないのであれば……

update A_TBL
set status = '4'
where seq_no in (
select seq_no
from (
select seq_no, key_2, status,
max(key_2) over (partition by key_1) as max_key_2
from A_TBL
)
where key_2 <> max_key_2 and status = '0'
)

でいいかもしれません。

この回答へのお礼

実験やURL紹介までしていただきありがとうございます。
副問合せでは制限がないことに確証が持てました。
「partition by」句でランク付けができるんですね。
SQLをもっと勉強したいと思います。

お礼日時：2009/12/23 11:42

No.2 回答者： dda167 回答日時： 2009/12/21 20:59

inリストの1000件制限はリテラルで指定したときですね。

No.1 回答者： dda167 回答日時： 2009/12/21 18:54

たとえば……

update A_TBL
set status = '4'
where key_2 <> (
select max(key_2)
from A_TBL wk
where A_TBL.key_1 = wk.key_1
)
and status = '0'

とか

merge into A_TBL
using (
select seq_no
from (
select seq_no, key_2, status,
max(key_2) over (partition by key_1) as max_key_2
from A_TBL
)
where key_2 <> max_key_2 and status = '0'
) wk
on (A_TBL.seq_no = wk.seq_no)
when matched then
update set status = '4'

といった感じですか？

# inを使いたくないのは更新が遅いからですか？
# 索引の状況や総件数などを挙げたほうが
# いいアドバイスをもらえるかもしれませんよ。

この回答への補足

ご回答ありがとうございます。
「not in の後ろのselect文で選択されるレコード数が、
1000件を超える可能性があるからinは使えない」
というご意見を頂きました。
(レコード数は最大で200,000件ぐらいあります)
ただし、ネットで検索したところ
in句でもカンマ区切りで指定した場合は1000件までで、
副問合せであれば問題なしという記述もありました。

どちらが正しいかわからず
とりあえずin句を使わない方法があればそれで対応してしまおうと
思った次第です。
(ちなみにINDEXはseq_noに作成されています。)

補足日時：2009/12/21 19:14