mysql 带有JOIN和WHERE子句的INSERT语句

rbpvctlc 于 12个月前发布在 Mysql

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

我有以下疑问

insert into T1 (code, code2, type, is_default, m_order, m_type, title, created_by)
select t.*
from    (select a.code from (select code from T2 where type = 171 and value = 'ABC') a,
         select b.code from (select code from T2 where type = 170 and value  = 'D') b,
         'Value', 1, 1, 1, 'Type', 'System'
        UNION ALL
        select c.code from (select code from T2 where type = 171 and value = 'DEF') c,
        select d.code from (select code from T2 where type = 170 and value = 'D') d,
       'Value', 1, 2, 2, 'Loc', 'System'
        ) t
where not exists (select ... (with joins));

错误：

SQL 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 'select b.code from (select code from T2 where type = 170 an' at line 4

我不知道为什么会有错误。我只是根据条件插入从其他表中获取的值。我遵循了this answer，它适用于从单个表插入/选择。但是当我试图从其他表中获取值时，它不起作用。
我做错了什么？

mysql

来源：https://stackoverflow.com/questions/76358115/insert-statement-with-joins-and-where-cluase

2条答案

按热度按时间

zf9nrax11#

您需要在用于获取单个值的每个子查询周围加上括号。
你也不需要这么多的巢。

INSERT INTO T1 (code, code2, type, is_default, m_order, m_type, title, created_by)
SELECT * FROM (
    SELECT (select code from T2 where type = 171 and value = 'ABC'),
           (select code from T2 where type = 170 and value  = 'D'),
           'Value', 1, 1, 1, 'Type', 'System'
    UNION ALL
    SELECT (select code from T2 where type = 171 and value = 'DEF'),
           (select code from T2 where type = 170 and value = 'D'),
           'Value', 1, 2, 2, 'Loc', 'System'
) AS x
WHERE NOT EXISTS (SELECT ...)

赞(0）回复(0）举报 12个月前

js81xvg62#

假设type和value列唯一标识T2行，您可以在不使用子查询的情况下获得相同的select查询输出：

SELECT D1.a, D2.b, D1.c, D1.d, D1.e, D1.f, D1.g, D1.h 
FROM 
(
  SELECT 
      MAX(code) a,
      'Value' c, 
      1 d, 
      CASE WHEN value = 'ABC' THEN 1 ELSE 2 END e, 
      CASE WHEN value = 'ABC' THEN 1 ELSE 2 END f, 
      CASE WHEN value = 'ABC' THEN 'Type' ELSE 'Loc' END g, 
      'System' h
  FROM T2
  WHERE type = 171 AND value IN ('ABC', 'DEF')
  GROUP BY value
) D1
CROSS JOIN 
(SELECT code b FROM T2 WHERE type = 170 AND value = 'D' ) D2

或者：

SELECT 
  MAX(CASE WHEN type = 171 AND value = 'ABC' THEN code END) a,
  MAX(CASE WHEN type = 170 AND value = 'D' THEN code END) b,
  'Value' c, 1 d, 1 e, 1 f, 'Type' g, 'System' h
FROM T2
UNION ALL 
SELECT 
  MAX(CASE WHEN type = 171 AND value = 'DEF' THEN code END) a,
  MAX(CASE WHEN type = 170 AND value = 'D' THEN code END) b,
  'Value' c, 1 d, 2 e, 2 f, 'Loc' g, 'System' h
FROM T2

可以在插入查询中使用这些查询。
demo

赞(0）回复(0）举报 12个月前

我来回答

mysql 带有JOIN和WHERE子句的INSERT语句

2条答案

相关问题

热门标签

最新问答