Question 177900 in Mysql Question posted in
The official MySQL documentation can be found here.

Can an INSERT Statement use JOINs and a WHERE Clause?

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?

Tags:

2

Answers


  1. 0

    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'
)
WHERE NOT EXISTS (SELECT ...)
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search