英文:
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
假设 type
和 value
列唯一标识了 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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论