このようなDELETEはできますか？

質問

こんにちは。DELETE文の削除条件について教えてください。

例えば下記のような２つのテーブルがあったとします。

ATABLE
KEY1(ID) KEY2(DATE) ・・・・・・・・・・
------------------------------------------------
100000 2004/09/01 ・・・・・・・・・・
200000 2004/09/01 ・・・・・・・・・・
300000 2004/09/01 ・・・・・・・・・・

BTABLE
KEY1(ID) KEY2(DATE) CODE ・・・・・・・・・・
------------------------------------------------
100000 2004/09/01 0 ・・・・・・・・・・
200000 2004/09/01 0 ・・・・・・・・・・
300000 2004/09/01 1 ・・・・・・・・・・

この２つのテーブルで、BTABLEのCODEが1のレコードにKEYで関連付くATABLEのレコードを削除したいのです。

プログラムの中では対応できるのですが、ＳＱＬ１文で実行させることはできるでしょうか？

とりあえず下記のように作ってみました(いたずらに長いかもしれませんが)。

DELETE FROM ATABLE WHERE

KEY1 IN (SELECT ATABLE.KEY1 FROM ATABLE,BTABLE WHERE ATABLE.KEY1 = BTABLE.KEY1 AND ATABLE.KEY2 = BTABLE.KEY2 AND BTABLE.CODE = 1) AND

KEY2 IN (SELECT ATABLE.KEY2 FROM ATABLE,BTABLE WHERE ATABLE.KEY1 = BTABLE.KEY1 AND ATABLE.KEY2 = BTABLE.KEY2 AND BTABLE.CODE = 1)

これで良い/悪い、もっと簡潔にできる、などありましたら是非お聞かせ願えますでしょうか？

よろしくお願いします。

回答No.4

mitoneko · ベストアンサー

＃３です。
　おそらく、私のｓｑｌは、標準ＳＱＬをサポートする大概のｄｂｍｓで有効だと思います。リストの比較は、どうかしら・・・この文法をsql serverがサポートしているかどうかはちょっと知りません。

　ちなみに、元のh164さんのｓｑｌですが、どうも違和感を感じるなと思ったら、やっぱり、まずいです。微妙な問題が潜んでいます。

atable
key1 key2 　・・・
--------------------------
10000 2004/09/01
10000 2004/09/02
20000 2004/09/01
20000 2004/09/02

btable
key1 key2 code
--------------------------
10000 2004/09/01 1
10000 2004/09/02 0
20000 2004/09/01 0
20000 2004/09/02 1

　というデータを考えたとき、おそらく、削除レコードは、10000,2004/09/01と20000,2004/09/02の二つのレコードだと思われるでしょうが・・・
　実は、全部のレコードが削除されます。
　たとえば、10000,2004/09/02のレコードですが、まず、第一の比較に置いて10000,2004/09/01が副ｓｅｌｅｃｔ文の結果セットに存在するので、key1の条件は真です。第二の比較でも同様に、副select文の結果セットに20000,2004/09/02が存在するので、この条件も真となります。というわけで、本来削除されるはずのない、10000,2004/09/02のレコードは、削除されます。

　複数フィールドにおける比較を、各フィールドの比較のAND条件で結合する時は、よほど注意をしないとこういう事があります。ご注意を。

　＃１および、＃２の方の回答では、この問題は発生しません。リストとして同時に比較しているからです。
　＃３の私のＳＱＬも大丈夫なはずです。レコードの比較時に、元のDELETE対象の現在の操作行を連動させているからです。（従って、副SELCT文のFROM句にはATABLEの指定がありません。これは、誤記ではありませんのでご注意を。）

お礼

ありがとうございました。とても参考になりました。

回答No.3

mitoneko

delete from atable a
where exist (select * from btable b
where a.key1=b.key1 and
a.key2=b.key2 and
b.code=1);
でいけそうな気がします。
今テストできないので自信なしとしておきます。

補足

回答を頂いた方々、どうもありがとうございます。

最初、私が書いた物より随分簡潔になりましたね。見易いです。

ちなみに、このSQLはSQL Serverでも動作しますでしょうか？(一応、両方の環境でやってみたいもので)

情報を小出しにしてしまい、申し訳ありません。

また、このカテゴリ(Oracle)にはあてはまらないかも知れませんが、よろしければご返答ください。

回答No.2

GoF

DELETE FROM ATABLE WHERE
( KEY1, KEY2 ) IN (
　SELECT KEY1, KEY2
　FROM BTABLE
　WHERE CODE=1
)

動作チェックをしておりませんが、大丈夫だと思います。

お礼

本来ならポイントを発行差し上げたいのですが、回答Ｎｏ１のご回答のna_kirajp様よりほんの数分の差ですが遅いご回答でしたので、ポイントを２名様にしかつけられないこともあり、今回はポイントの発行はなしとさせて頂きます。
ですが、感謝の気持ちに変わりないということはご理解下さい。ありがとうございました。

回答No.1

na_kirajp

こんにちは

提示のＳＱＬで基本的に考え方は合っているし問題ないと思いますが、他の例として

delete from ATABLE where (ATABLE,KEY1,ATABLE,KEY2)
in (select KEY1,KEY2 from BTABLE where code=1);

なんて言うのはどうでしょうか？
違いは、BTABLEを一度参照するだけでATABLEを消せるので
パフォーマンス的には多少良いかと・・・

お礼

ありがとうございました。
回答Ｎｏ２のGoF様と同内容のご回答でしたが、回答時間の関係でこちらにポイントを差し上げることにします