WEB相談室

Webページ作成に関しての何でも掲示板です。

タイトル:レコードがあればUPDATE、なければINSERTする

0:[投稿] kei [2004/03/05 11:50 ][環境:Win2000 オラクル]

こんにちは。
よろしくお願いします。
タイトルどおりなのですが、SQLでレコードがあるかどうかはどう判別するのでしょうか?
EXISTSというのを見つけましたが、どうもなんか違う気がするし・・・。
いったんSELECTしてみてレコードが戻ってくるかどうか調べた上でUPDATEかINSERTか判断するのでしょうか?
それとも一発でやってくれる文があるのでしょうか?


1:[回答] B-Cus [2004/03/05 18:18 ]

これがよい方法かどうかは知りませんが、わたしの場合は INSERT
して、一意制約違反なら UPDATE、とすることが多いです。

Oracle9i 以降なら merge も使えます。
http://homepage2.nifty.com/sak/w_sak3/doc/sysbrd/sq_kj09_1.htm


2:[回答] AC [2004/03/05 22:59 ]

PL/SQLを使うとか、その他の手続き型言語を使うのがいつも使う方法ですね。

どうしてもSQLだけでやるのであれば、selectでrowidを取り出して、あれば
そのrowidを使ってupdate、なければinsertとするのが速そう(な気がする)。


3:[完了] kei [2004/03/08 13:00 ]

お返事ありがとうございます。
勉強を始めたばかりでPL/SQLというのはちょっと分からないのですが、
バージョンを調べてみたところOracle9iでしたので、
mergeを使って試してみたいと思います。


4:[質問] kei [2004/03/09 13:42 ]

すみません、mergeを色々と試しているのですが、弄るたびに違うエラーがでてきてもうわけわかりません。
テーブルはtmptblひとつ。
キーkey01とkey02でチェックして、レコードがあればdata01をカウントアップ、data02の値を更新します。
なければ新規にレコードを追加します。
以下のように書いてみたのですが、
--------------------------------
SQL> merge into tmptbl
 2  using tmptbl
 3  on (
 4  key01 = '01'
 5  and key02 = '02'
 6  )
 7  when matched then
 8  update set
 9  data01 = data01 + 1,
10  data02 = 'test'
11  when not matched then
12  insert values (
13  key01 = '01',
14  key02 = '02',
15  data01 = 1,
16  data02 = 'test'
17  );
key01 = '01',
     *
7行でエラーが発生しました。
ORA-00918: 列の定義が未確定です。
-----------------------------------
ほかにも一意制約に反しているなどとでたりします・・・。
何がおかしいのでしょうか?


5:[回答] B-Cus [2004/03/10 11:21 ]

> key01 = '01',
>      *
> 7行でエラーが発生しました。
> ORA-00918: 列の定義が未確定です。

馬鹿な Oracle にしては親切なエラーメッセージなので、
よく読みましょう。

この SQL では
  merge into tmptbl using tmptbl
と tmptbl が 2つ出てくるので、key01 といわれても
どっちのテーブルのことなのか Oracle には判断できない
わけです。

> http://homepage2.nifty.com/sak/w_sak3/doc/sysbrd/sq_kj09_1.htm
にあるように、
  merge into テーブル a using テーブル b
などとテーブルに異なる別名をつけ、a.key01 や b.key01 と
しましょう。

> 12  insert values (
> 13  key01 = '01',
merge 文使ったことないですけど、どう見てもエラーになりそうな予感が。

> 勉強を始めたばかりでPL/SQLというのはちょっと分からないのですが、

begin
  begin
     insert ...;
  exeption
     when no_data_found then
       update ....;
     when others then
       エラー処理;
  end;
end;

的な感じですかね。動作確認はしていません。


6:[回答] kei [2004/03/10 12:10 ]

ありがとうございます。
また弄ってみたのですが、
-------------------------------
SQL> merge into tmptbl a
 2  using tmptbl b
 3  on (
 4  b.key01 = '01'
 5  and b.key02 = '02'
 6  )
 7  when matched then
 8  update set
 9  a.data01 = a.data01 + 1,
10  a.data02 = 'test'
11  when not matched then
12  insert values (
13  '01',
14  '02',
15  1,
16  'test'
17  );
merge into tmptbl a
*
1行でエラーが発生しました。
ORA-00001: 一意制約(***)に反しています
-----------------------------------------
何がいけないのか分かりますでしょうか?

今回はプログラムから発行する形になるので、PL/SQLは使えなさそうな感じです。
とりあえずmergeでダメなら、>2のまずselectして・・・というのを使おうかと思います。
とはいえこのまま諦めてしまうのも癪なので、どなたかアドバイスをお願いします。


7:[回答] B-Cus [2004/03/10 13:19 ]

> ORA-00001: 一意制約(***)に反しています

「一意制約」をキーワードに、自分で検索してください。DB の
初歩ですので、いくらでも情報が見つかるはずです。

> 今回はプログラムから発行する形になるので、PL/SQLは使えなさ
> そうな感じです。

PL/SQL を使えって言っているわけではありませんが、少なく
とも Java、Pro*C、Perl からは PL/SQL をコールできます。
一般的にプログラムからでも利用可能なものです (VBA とか
から呼べるかは知りませんが、たぶん呼べるんじゃないですかね)。


8:[回答] kei [2004/03/10 14:11 ]

一意制約は検索して大方の意味はわかったと思うのですが、
それで具体的に何が一意制約違反なのかが分からないのです。
上の例で行くと、key01とkey02がキーになっています。

プログラムがVBなので、調べてみたらPL/SQLは呼べるようです。
しかしながらそこまで勉強をするだけの時間的余裕がもうないようです。
とりあえずselectからチェックする方法のコードを採用することにします。
もしmergeの方で何かありましたら、引き続きお願いします。


9:[回答] B-Cus [2004/03/10 14:54 ]

>>1
> Oracle9i 以降なら merge も使えます。
これ、嘘でした。すみません。

初めて merge 文を使ってみましたが、
  on (
    b.key01 = '01'
    and b.key02 = '02'
  )
のところに引っかからない場合、何のアクションも起こしてくれ
ないようです。よって、今回の用途には使えないことがわかりました。

merge 文は
  あるデータが必ず存在するテーブル A
  あるデータが存在するかどうかわからないテーブル B
という前提条件があり、テーブル B に対して
 - データが存在すれば UPDATE
 - 存在しなければ INSERT
としたい場合に使用するもの、ではないかと思います。


10:[回答] B-Cus [2004/03/10 15:44 ]

> のところに引っかからない場合、何のアクションも起こしてくれ
> ないようです。
ってのも変だなー、引っかからない場合に何もしなかったら
意味ないじゃんと思っていろいろやってみたところ、

 merge into t1
 using t2
   on (t1.c=t2.c)
 when matched then update ...
 when not matched then insert ...

というのは、

  -- PL/SQL っぽいコード。動きません。
  select * from t2; -- ここは必ず全行を取得する。
  loop
     fetch into rec2;
     exit when notfound;

     -- 取得した t2 のレコードを用いて、t1 を検索
     select * from t1
        where c = :rec2.c;

     -- 存在しなければ INSERT。存在すれば UPDATE。
     if notfound then
         insert into t1;
     else
         update t1;
     end if;
  end loop;

的な動きと等価なのではないか? と思いました。

だから t2 が空だと何も行なわれないし、もし t2 に
2行存在すると、INSERT または UPDATE が 2回発行される。
一意制約もそれのせい?


間違ってたらご指摘願います。


11:[回答] B-Cus [2004/03/10 16:06 ]

しつこくてすいません。

> だから t2 が空だと何も行なわれないし、もし t2 に
> 2行存在すると、INSERT または UPDATE が 2回発行される。
ということは、t2 が常に 1レコードになるようにすれば、
今回の目的は達成できるかも、というわけで、

 merge into tmptbl a
  using (select * from dual) b -- とにかく 1レコードだけ取得するようにする
  on (
    a.key01='01' and a.key02 = '02' -- ここでは a だけ考える。b は関係ない。
  )
  when matched then
    update set
     a.data01 = a.data01 + 1,
     a.data02 = 'test'
  when not matched then
    insert values ('01', '02', 1, 'test')

としてみたところ、一応うまく動いているようです。

ただ、実現はできるようですが merge 本来の使い方ではないと
思うので、わたし的にはこの書き方は NG かなぁと思います。


12:[完了] kei [2004/03/10 16:17 ]

検証してくださったようで、ありがとうございます。
うーーーん、なんだかとてもややこしい話になっていますね。
良く分からない人間が良く分からないままに「NGかなぁ」という
判断がなされているものを組み込んでしまうのは後々困ったことに
なりそうな気がするので、今回はselectから分岐させる方法を採用します。
色々とありがとうございました。

回答(必須): 状態:

お名前(必須):

e-mail:

URL:




[戻る]

ChaichanPAPA's World