« MERGE文 #1 - 重複行の削除 | トップページ | iMovie'08 で YouTube »

2007年9月16日 (日)

MERGE文 #2 - 同一表でマージ?!

merge文を利用したついでに、merge文のTipsも1つ紹介しておきましょう。(Oracle9iでmerge文が追加された際、OTN-Jでも同じ話題があったので投稿したネタですが使えるTipsなのでね。。今そのスレッドの日付を見たら、なんと、2002年。。。時の経つのは早いもので。。。)

ということで、今回はMERGE文のTipsを一つ。

この内容は、2002年にOracle9iでmerge文がサポートされた頃にOTN-Jのフォーラムに投稿した内容ですが、「同一表を使ってMERGE文を実行する」というTipsです。

MERGE文ばOracle9iから登場するということで、リリース直後からMERGE文についてはいろいろと実験をしていて、通常2つの表を使うMERGE文を1つの表だけで行えないのか? とあれやこれや試し、dual表を上手に利用すれば実現可能であることがわかっていた。ほぼその頃にOTN-Jのフォーラムに投稿された内容に。。

単一の表だけでMERGE文を実行できるか?  というスレッド

があったのでレスを付けたのが2002年。

投稿したのはWindowsで実験したログだったが、MERGE文を試していた環境は日本ではリリースされなかった? Oracle9i R2 Developer Preview for MacOSX Server (Jaguar)上だったんですよ〜。マニアック過ぎるけど。。。。

例では、1つの表に対して、データが存在していなければデータ登録、存在していれば更新するという処理をMERGE文だけで行っています。
通常2つの表を利用するMERGE文ですが、dual表をダミーとして利用すれば、1つの表に対してMERGE文を実行できるというわけです。
(MERGE文の用途は結構広いので、知っていて損はないと思いますよ〜。)
ただし、内部では複雑なことをやっているようなので、事前にBUG情報などをチェックしておくこともよいかもしれません。(不具合により意図した動作をしない場合を想定して、代替案を考えなくてはなりませんからね。)

ということで、2002年に投稿した内容より。

Oracle9i 9.0.1.3.0 EE for windowsで試したログ

SQL> set linesize 132
SQL> set pagesize 500
SQL> column create_timestamp format a30
SQL> column update_timestamp format a30
SQL> column id format a4
SQL> column data format a4

SQL> create table sample_table
2 (
3 id varchar2(10) primary key,
4 data varchar2(40),
5 create_timestamp timestamp,
6 update_timestamp timestamp
7 );

表が作成されました。

SQL> create table sample_table_temp
2 (
3 id varchar2(10) primary key,
4 data varchar2(40),
5 create_timestamp timestamp,
6 update_timestamp timestamp
7 );

表が作成されました。

SQL> insert into sample_table values('A001','HOGE',systimestamp,null);

1行が作成されました。

SQL> insert into sample_table values('A002','SOLE',systimestamp,null);

1行が作成されました。

SQL> insert into sample_table values('A003','DOLE',systimestamp,null);

1行が作成されました。

SQL> insert into sample_table values('A004','KOLE',systimestamp,null);

1行が作成されました。

SQL> commit;

コミットが完了しました。

SQL> insert into sample_table_temp values('A003','どれ',systimestamp,null);

1行が作成されました。

SQL> insert into sample_table_temp values('A002','それ',systimestamp,null);

1行が作成されました。

SQL> insert into sample_table_temp values('B001','なに',systimestamp,null);

1行が作成されました。

SQL> insert into sample_table_temp values('B002','ほれ',systimestamp,null);

1行が作成されました。

SQL> commit;

コミットが完了しました。



※まずは、2つの表の単純なマージでMERGE文の感覚を掴む!

SQL> merge into sample_table
2 using sample_table_temp
3 on (sample_table.id = sample_table_temp.id)
4 when matched then
5 update set
6 sample_table.data = sample_table_temp.data,
7 sample_table.update_timestamp = systimestamp
8 when not matched then
9 insert values
10 (
11 sample_table_temp.id,
12 sample_table_temp.data,
13 systimestamp,
14 null
15 );

4行がマージされました。

SQL> select * from sample_table;

ID DATA CREATE_TIMESTAMP UPDATE_TIMESTAMP
---- ---- ------------------------------ ------------------------------
A001 HOGE 02-05-23 13:24:59.000001
A002 それ 02-05-23 13:25:59.000001 02-05-23 13:40:59.000000
A003 どれ 02-05-23 13:26:20.000001 02-05-23 13:40:59.000000
A004 KOLE 02-05-23 13:26:57.000000
B002 ほれ 02-05-23 13:40:59.000000
B001 なに 02-05-23 13:40:59.000000

6行が選択されました。

SQL> commit;


SQL> select * from sample_table;

ID DATA CREATE_TIMESTAMP UPDATE_TIMESTAMP
---- ---- ------------------------------ ------------------------------
A001 HOGE 02-05-23 13:24:59.000001
A002 SOLE 02-05-23 13:25:59.000001
A003 DOLE 02-05-23 13:26:20.000001
A004 KOLE 02-05-23 13:26:57.000000


SQL> select * from sample_table_temp;

ID DATA CREATE_TIMESTAMP UPDATE_TIMESTAMP
---- ---- ------------------------------ ------------------------------
A003 どれ 02-05-23 13:27:40.000001
A002 それ 02-05-23 13:28:04.000001
B001 なに 02-05-23 13:28:28.000001
B002 ほれ 02-05-23 13:28:56.000001



※本題、dual表をダミーとして同一表でMERGE文を利用する。以下の例では、同一キーがないため、挿入されます。
SQL> merge into sample_table src
2 using ( select 'C001' id from dual ) dummy
3 on ( src.id = dummy.id )
4 when matched then
5 update set
6 data = 'だ!',
7 update_timestamp = systimestamp
8 when not matched then
9 insert values
10 (
11 'C001',
12 'だ〜',
13 systimestamp,
14 null
15 );


1行がマージされました。

SQL> select * from sample_table;

ID DATA CREATE_TIMESTAMP UPDATE_TIMESTAMP
---- ---- ------------------------------ ------------------------------
A001 HOGE 02-05-23 13:24:59.000001
A002 それ 02-05-23 13:25:59.000001 02-05-23 13:40:59.000000
A003 どれ 02-05-23 13:26:20.000001 02-05-23 13:40:59.000000
A004 KOLE 02-05-23 13:26:57.000000
B002 ほれ 02-05-23 13:40:59.000000
B001 あ! 02-05-23 13:40:59.000000 02-05-23 14:06:28.000000
C001 だ〜 02-05-23 14:37:08.000001

7行が選択されました。


※さらに同一文を実行すると、同一キーがあるため更新されます。
SQL> merge into sample_table src
2 using ( select 'C001' id from dual ) dummy
3 on ( src.id = dummy.id )
4 when matched then
5 update set
6 data = 'だ!',
7 update_timestamp = systimestamp
8 when not matched then
9 insert values
10 (
11 'C001',
12 'だ〜',
13 systimestamp,
14 null
15 );

1行がマージされました。

SQL> select * from sample_table;

ID DATA CREATE_TIMESTAMP UPDATE_TIMESTAMP
---- ---- ------------------------------ ------------------------------
A001 HOGE 02-05-23 13:24:59.000001
A002 それ 02-05-23 13:25:59.000001 02-05-23 13:40:59.000000
A003 どれ 02-05-23 13:26:20.000001 02-05-23 13:40:59.000000
A004 KOLE 02-05-23 13:26:57.000000
B002 ほれ 02-05-23 13:40:59.000000
B001 あ! 02-05-23 13:40:59.000000 02-05-23 14:06:28.000000
C001 だ! 02-05-23 14:37:08.000001 02-05-23 14:37:30.000000

7行が選択されました。

|

トラックバック


この記事へのトラックバック一覧です: MERGE文 #2 - 同一表でマージ?!:

コメント

コメントを書く