MERGE文 #1 - 重複行の削除 Tweet
ははは、久々のOracleネタです。
最近、OTN-Jのフォーラムを閲覧したり返信したりする回数を控えている。Feedの概要だけ眺めている事が多くなってきていることも影響しているが、興味をそそる内容のものが少ないことや、本家US OTNのフォーラムや Oracle Aceの方々のブログのほうが面白いのもその理由の一つ）
で、しばらく振りで、面白そうなネタだったのでレスを付けた。
ちょいと前のネタになるが、
定義の同じ２つの表のデータを纏めて表示したい。ただし、重複データを除く。また、重複していた件数も取得したい。。。。
というスレッド。
ただ質問内容を読むと、最近よくある実行環境（Oracleのバージョンなど）を記述せず、●●したいというタイプの質問。
（単に書き忘れかもしれないが、問題が発生している環境やOracleのバージョンを書かずに意図した答えを待ってるなんて時間がもったいないじゃん。そこんとこをちゃんと公開していれば、もっと早く意図した解答得られたと思うが。。）
そして、最後まで、実行環境に関する情報は未提示のままスレッドは終わるのであった〜〜（笑）
ということで質問内容など詳細は下記URLで
http://otn.oracle.co.jp/forum/message.jspa?messageID=35016369&tstart=15
私がレスした内容は以下のようなもの。
（質問内容のカラム名が ”日本語”だったので例題も引用識別子で囲んだ日本語の表／列名にしてあります。私は実際の開発では使いませんけどね、日本語オブジェクト名なんて。。）
主キー制約や一意制約、その他の制約などが提示されていないので勝手に作ってありますが、merge文を使えば出来ますよ。
尚、Oracle10gを利用していものと仮定していますので、Oracle9i以前である場合、merge文でdeleteを実施することはできません。また、暗黙カーソルを利用していますので、sql%rowcount属性で重複行数（マージした行数）を取得しています。
（明示カーソルにした場合は、カーソル名%rowcount）他の方法もあると思いますので、あとは処理速度等比較し、よい方を採用すれば良いと思います。
詳細は、SQLや、PL/SQLリファレンスマニュアルの merge文及び、カーソル属性あたりを確認してみてください。
SCOTT> create table "テーブルＡ"
2 ("商品コード" char(5) not null,
3 "送品年月日" date not null,
4 "個数" number(4) not null
5 );
表が作成されました。
SCOTT> create table "テーブルＢ" as select * from "テーブルＡ";
表が作成されました。
SCOTT>
データの登録・・・・中略・・・・
SCOTT> alter session set nls_date_format = 'yyyy/mm/dd';
セッションが変更されました。
SCOTT> select * from "テーブルＡ";
商品コード 送品年月日 個数
-------- ---------- ----------
00001 2007/07/18 5
00002 2007/07/18 6
00003 2007/07/19 7
00004 2007/07/18 5
00005 2007/07/20 4
00001 2007/07/19 8
00002 2007/07/19 2
00003 2007/07/20 0
8行が選択されました。
SCOTT> select * from "テーブルＢ";
商品コード 送品年月日 個数
-------- ---------- ----------
00001 2007/08/18 5
00002 2007/08/18 6
00003 2007/07/19 9
00004 2007/08/18 5
00005 2007/08/20 4
00001 2007/07/19 10
00002 2007/08/19 2
00003 2007/08/20 0
8行が選択されました。
SCOTT> set serveroutput on
SCOTT> l
1 begin
2 merge
2 into "テーブルＡ" dest
3 using "テーブルＢ" src
4 on (
5 dest."商品コード"=src."商品コード"
6 and dest."送品年月日"=src."送品年月日"
7 )
8 when matched then update set dest."個数"=-1
9 delete where dest."個数"=-1;
10 dbms_output.put_line(sql%rowcount);
11* end;
SCOTT> /
2
PL/SQLプロシージャが正常に完了しました。
SCOTT>
SCOTT> select * from "テーブルＡ";
商品コード 送品年月日 個数
-------- ---------- ----------
00001 2007/07/18 5
00002 2007/07/18 6
00004 2007/07/18 5
00005 2007/07/20 4
00002 2007/07/19 2
00003 2007/07/20 0
6行が選択されました。
SCOTT>
マニュアルにもしっかり記述されているが、merge文のdelete句を利用する場合のポイントは、マージ条件に一致し、update句で更新対象となったデータがdelete句の処理対象となるという点だ。delete句がupdate句とは個別に実行されるわけではない。
前述の例では、update句でマージ条件に一致したデータ（この場合は重複しているデータ）の”個数”を-1に変更し、delete句で "個数” = -1となったデータ（重複したデータ）を削除している。
このことを理解していないと意図した結果を得られないので要注意。（一度理解してしまえば間違ることもなくなると思うが。。。）
merge文を利用したついでに、merge文のTipsも１つ紹介しておきましょう。(Oracle9iでmerge文が追加された際、OTN-Jでも同じ話題があったので投稿したネタですが使えるTipsなのでね。。
今そのスレッドの日付を見たら、なんと、２００２年。。。時の経つのは早いもので。。。）
では、次回へつづく。
| 固定リンク | 0
コメント