IF NOT EXISTを使用するINSERT文

質問

格納するデータの性質上、主キーや一意キーを設定できないテーブル（testtable）に、ある条件を満たすデータが既にあればデータを登録しない、というINSERT文（下記）を実行したいと考えています。

---------------------------
IF NOT EXISTS (
SELECT * FROM testtable
WHERE Entity1='entity1'
AND Entity2='entity2'
AND localId='myid'
AND subsysId='subsysid'
AND deactivationDate is NULL
)
INSERT INTO shibpid (Entity1, Entity2, localId, subsysId)
VALUES ('entity1', 'entity2', 'myid', 'subsysid)
ELSE PRINT 'already exist.';
---------------------------

しかし、このSQL文を以下のように実行するとエラーがでてしまいます。
---------------------------
>mysql -u root -p testdb
Enter password: ******(パスワードを入力)

mysql> IF NOT EXISTS (
-> SELECT * FROM testtable
-> WHERE Entity1='entity1'
-> AND Entity2='entity2'
-> AND localId='myid'
-> AND subsysId='subsysid'
-> AND deactivationDate is NULL
-> )
-> INSERT INTO shibpid (Entity1, Entity2, localId, subsysId)
-> VALUES ('entity1', 'entity2', 'myid', 'subsysid)
-> ELSE PRINT '111';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS (SELECT * FROM testtable WHERE Entity1='entity1' at line 1
mysql>
---------------------------

文法エラー、といわれても、どこが違うのか全くわからず、非常に困っています。
もしや、IF NOT EXISTS は使えない、あるいはこのような使い方はできないのでしょうか？

回答No.4

masa6272 · ベストアンサー

IF EXISTS などという文は、SQLにはありません。
これは、条件に書くものです。
SQL文は集合演算的な言語ですので、こういった手続き型の記述はストアードプロシージャなどで行います。

ただ。こういった手はあります。

まず、1行だけのテーブルを作ります。これはOracleで言えばdualのようなものです。FROM句にダミーで使います。

CREATE TABLE onerow(x int);
INSERT INTO onerow values(0);

そして次のようなSQL文を発行します。
INSERT INTO shibpid (Entity1, Entity2, localId, subsysId)
(SELECT 'entity1', 'entity2', 'myid', 'subsysid' FROM onerow
WHERE NOT EXISTS (
SELECT * FROM testtable
WHERE Entity1='entity1'
AND Entity2='entity2'
AND localId='myid'
AND subsysId='subsysid'
AND deactivationDate is NULL))

補足

これを試してみたところ、期待する動作を得ることができました！

ダミーのテーブルに問い合わせる分、資源消費や処理速度が心配ですが・・・それにしても、こんな方法もあったとは。
勉強になりました。

回答No.7

masa6272

まだ開いているようですので・・・
効率は問題ないですよ。
条件式のNOT EXISTSの方がほとんどの時間を食うでしょう。
それに比べれば、無視できるコストだと思います。

お礼

たびたびの補足説明、有難うございます。

> 条件式のNOT EXISTSの方がほとんどの時間を食うでしょう。
意外にもダミーテーブルを使った問い合わせの方が早いのですね。
また１つ、勉強になりました。

最後までお付き合いくださり、本当にありがとうございました。

回答No.6

masa6272

#4です。
補足です。
MySQLでは、FROM句のないSELECT文が書けます。
SELECT 1,2,3;
を実行すると、1,2,3からなる1行が返されます。
しかし、WHERE句を書く時にはFROM句がないとシンタックスエラーと怒られます・・・
で、このような無害なテーブルを使ってます。
この解は、ほとんどのRDBMSで動きます。Oracleの場合は、onerowと同じ役割を果たすdualというテーブルが最初からありますので、そちらを使えます。

回答No.5

yambejp

この場合、適当なフィールドにUNIQUE属性をつけておいて
INSERT IGNORE INTOでインサートしてやるのが現実的かと

補足

最初、一意のデータを表現できる、最低限の複数のフィールドでUNIQUEキーを設定することを考えました。
（データの性質上、単独のフィールドで一意にならないのです。）

しかし、その最低限の複数のフィールドのなかにNULLが入るものがあり、NULLが入ると一意性の判定をしてくれないらしく、そのフィールドの値がNULLであれば同じデータが幾らでも登録できてしまいました。

このような事情から、キー制約以外の方法を探していました。

回答No.3

INTLINSIDE

記憶によると IF NOT EXIST は、CREATE TABLE などにしか使えなかったと思います。

英文ですが、代替手段はあるようです。
http://bogdan.org.ua/2007/10/18/mysql-insert-if-not-exists-syntax.html

フロー制御を駆使すれば出来るような気もするのですが。
http://dev.mysql.com/doc/refman/5.1/ja/control-flow-functions.html

（過去にやったことがあると思ったので自分のソースを見てみたところ
アプリケーション側で処理してました）

5.0だとストアドも使えるようです。

補足

> 英文ですが、代替手段はあるようです。
ここに挙げられた方法の動作は私の希望とちょっと違うようです。
（私のテーブルには主キーなどが無いので、ここで紹介されている方法のどちらでも、どんどん登録データが増えてしまうのです。）

ただ、文中にあげられている、ダミー操作（多分、#4の方が述べているのと同じ感じだと思います）は検討の価値がありそうです。

> フロー制御を駆使すれば出来るような気もするのですが。
こちらは、現状、「そんな気がするけど、いまいち使い方がわからないなぁ」状態です。
もう少し自分で調べてみて、必要なら改めて質問したいと思います。

> 5.0だとストアドも使えるようです。
ストアドプロシージャの使用は検討したのですが、できるだけDBの種類に依存しない方法を採りたい、との思いから、これは最終手段ということにしています。

回答No.2

chukenkenkou

>IF NOT EXISTS

これは、SQL ServerのT-SQLの構文では？

回答No.1

yambejp

＞IF NOT EXISTS

の構文は具体的になにを参考になさっているのでしょうか？
また、ご利用になっているMySQLのバージョンはいくつでしょうか？

補足

以前に使用したことがある、SQL SERVERでこのようなことができた気がするのだけど・・・というのと、MySQLでもDROP DATABASE文などでIF EXISTS句を使用しているので使えるのでは？という、２つから質問のようなクエリを記述しました。

使用しているMySQLは5.0です。