私と私の猫の他は誰でも隠し事を持っている

野良プログラマ発、日々のアウトプット

「INSERT … SELECT … WHERE NOT EXIST …」を少し深掘りしてみた

MySQL で INSERT する際に、所定条件のレコードが既に存在しない時のみ行いたい、ってのはよくある要求のようです。調べたら、こんなハック↓で何とかなる模様。

stackoverflow.com

成る程、INSERT … SELECT 構文の SELECT を工夫する訳か。ただ自分の場合、上記記事の例に倣うと

INSERT INTO table_listnames (name, address, tele)
SELECT * FROM (SELECT 'Rupert', '', '') AS tmp
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name = 'Rupert'
) LIMIT 1;


みたいに、複数列に同じ値をセットしたかったのですが、それだと「ERROR 1060 (42S21): Duplicate column name ''」と怒られちゃいます。これには

INSERT INTO table_listnames (name, address, tele)
SELECT * FROM (SELECT 'Rupert' AS `name`, '' AS `address`, '' AS `tele`) AS tmp
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name = 'Rupert'
) LIMIT 1;

と、きっちり列名を付与してやれば良いみたいです。さて問題は解決したのですが、この SQL、SELECT 文が二重になってて何だか分かったような分かんないような。フツーに

SELECT 'Rupert' AS `name`, '' AS `address`, '' AS `tele`
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name = 'Rupert'
) LIMIT 1;

じゃダメなの? と試してみるとエラー。FROM 句が無いと WHERE 句は使えないのか… それじゃ

SELECT 'Rupert' AS `name`, '' AS `address`, '' AS `tele` FROM tmp
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name = 'Rupert'
) LIMIT 1;

では? 「tmp なんてテーブルねーぞ」と怒られるなぁ(当たり前)。ここで「MySQL テーブル ダミー」等とぐぐってみたら、MySQL には DUAL というダミーテーブルがあるようです↓。

rnk.mitelog.jp

という訳で

INSERT INTO table_listnames (name, address, tele)
SELECT 'Rupert' AS `name`, '' AS `address`, '' AS `tele` FROM DUAL
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name = 'Rupert'
) LIMIT 1;

で上手く行きました! 少しシンプルになったぞいww