mysql-insert with subquery if not exists如果不存在

db2dz4w8 于 2021-06-18 发布在 Mysql

关注(0)|答案(2)|浏览(310)

我很困惑，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服务器版本对应的手册，以了解在“where not exists（select*from matches where fk\u object\u id=oid and fk\u lookout\ux”第12行使用的正确语法
包含所有联接的大型select查询本身运行良好：

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

你们比我看得清楚吗？可能：）
谨致问候

sql mysql

来源：https://stackoverflow.com/questions/52704731/mysql-insert-with-subquery-if-not-exists

2条答案

按热度按时间

ig9co6j11#

多亏了@gordon linoff，查询现在可以工作了！
非常感谢！
这是工作查询：

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 JOIN object ON object.id = m.`fk_object_id` JOIN lookout ON lookout.id = m.`fk_lookout_id` WHERE m.fk_object_id = object.id AND m.fk_lookout_id = lookout.id)
    GROUP BY o.id;

赞(0）回复(0）举报 2021-06-18

c0vxltue2#

为什么你的名字周围有括号 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;

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

赞(0）回复(0）举报 2021-06-18

我来回答

mysql-insert with subquery if not exists如果不存在

2条答案

相关问题

热门标签

最新问答