INSERT语句与JOIN和WHERE子句

huangapple go评论75阅读模式
英文:

INSERT Statement with JOINs and WHERE Cluase

问题

以下是您要翻译的部分:

我有以下查询

    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错误 [1064] [42000]:您的SQL语法有错误;请检查与您的MySQL服务器版本相对应的手册,了解正确的语法用法,位于'select b.code from (select code from T2 where type = 170 an'在第4行附近

我不确定为什么会出现错误。我只是根据条件从其他表中获取值并插入。我遵循了[这个答案][1],它适用于从单个表中进行插入/选择。但是,当我尝试从其他表中获取值时,它不起作用。

我做错了什么?

请注意,我已将文本内容翻译为中文,如您要求的那样,但我没有回答您是否要翻译的问题。如果您需要进一步的翻译或其他帮助,请告诉我。

英文:

I have the following query

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));

Error:

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

I am not sure why is there any error. I am just inserting values fetched from other tables based on condition. I followed this answer which works for insert/select from a single table. But when I try to get value from other tables it doesn't work.

What am I doing wrong?

答案1

得分: 2

需要在每个用于获取单个值的子查询周围加上括号。

您也不需要这么多的嵌套。

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 ...)
英文:

You need parentheses around each subquery that's being used to get a single value.

You also don't need so much nesting.

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 ...)

答案2

得分: 0

假设 typevalue 列唯一标识了 T2 行,你可以在不使用子查询的情况下获得与你的选择查询相同的输出:

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

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

英文:

Supposing that the type and value columns uniquely identify the T2 rows, you could get the same output of your select query without using subqueries:

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

Or:

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

You can use these queries in your insert query.

demo

huangapple
  • 本文由 发表于 2023年5月29日 22:19:18
  • 转载请务必保留本文链接:https://go.coder-hub.com/76358115.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定