时间戳

首页 > 解决方案 > MySQL - 如果不存在，则使用子查询插入

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

问题描述

我很困惑，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

我总是收到以下错误：

您的 SQL 语法有错误；检查与您的 MariaDB 服务器版本相对应的手册，以在第 12 行的 'WHERE NOT EXISTS (SELECT * FROM matches WHERE fk_object_id = oid AND fk_lookout_' 附近使用正确的语法

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

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

你们看得比我清楚吗？大概 ：）

亲切的问候

标签： mysqlsql

解决方案

为什么你的周围有括号select？大概，您打算：

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;

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

推荐阅读