MySQL - 如果不存在则使用子查询插入

Question

我很困惑，SQL 不是我的强项。

我一直在查看以下答案，但我可以让自己的查询发挥作用:

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

并且

INSERT INTO `table` (value1, value2) 
SELECT 'stuff for value1', 'stuff for value2' FROM DUAL
WHERE NOT EXISTS (SELECT * FROM `table` 
      WHERE value1='stuff for value1' AND value2='stuff for value2') 
LIMIT 1 

我想将子查询的结果插入到新表(匹配)中，这是我的查询:

INSERT INTO matches (fk_object_id, object_adress, fk_lookout_id, lookout_name)
    (SELECT o.id as oid, o.adress as oa, l.id as lid, l.first_name as lfn 
    FROM geo_lookout gl
    JOIN geo_object go ON go.`fk_geo_id` = gl.`fk_geo_id`
    JOIN object o ON o.id = go.`fk_object_id`
    JOIN attri_object ao ON ao.`fk_object_id` = go.`fk_object_id`
    JOIN attri_lookout al ON al.`fk_attri_id` = ao.`fk_attri_id`
    JOIN lookout l ON l.`id` = al.`fk_lookout_id`
    WHERE o.`have_size` <= l.`max_size`
    AND o.`have_size` >= l.`min_size`
    GROUP BY o.id)
WHERE NOT EXISTS (SELECT * FROM matches WHERE fk_object_id = oid AND fk_lookout_id = lid)
LIMIT 1

我总是收到以下错误:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE NOT EXISTS (SELECT * FROM matches WHERE fk_object_id = oid AND fk_lookout_' at line 12

带有所有 JOINS 的大型 SELECT 查询本身运行良好:

oid        oa          lid         lfn
45    aGoodStreet       32    Andrew Phillis
44    aGoodStreet       32    Andrew Phillis

你们看得比我清楚吗？可能:)

亲切的问候

Answer 1

为什么你的选择两边有括号？据推测，您打算:

INSERT INTO matches (fk_object_id, object_adress, fk_lookout_id, lookout_name)
    SELECT o.id as oid, o.adress as oa, l.id as lid, l.first_name as lfn 
    FROM geo_lookout gl
    JOIN geo_object go ON go.`fk_geo_id` = gl.`fk_geo_id`
    JOIN object o ON o.id = go.`fk_object_id`
    JOIN attri_object ao ON ao.`fk_object_id` = go.`fk_object_id`
    JOIN attri_lookout al ON al.`fk_attri_id` = ao.`fk_attri_id`
    JOIN lookout l ON l.`id` = al.`fk_lookout_id`
    WHERE o.`have_size` <= l.`max_size` AND
          o.`have_size` >= l.`min_size` AND
          NOT EXISTS (SELECT * FROM matches m WHERE m.fk_object_id = o.oid AND m.fk_lookout_id = l.lid)
    GROUP BY o.id
    LIMIT 1;

请注意，我必须在子查询中猜测列的来源。限定所有列引用。