Syntax Error when Creating a Temporary Table Using A Subquery That Contains A Union All



  • So what I'm trying to do is create a temporary table with a unioned set of results that have distinct conditions. The information is largely the same, just in different places that need to be matched.

    My query is below;

    DROP TABLE IF EXISTS temp_data;
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_data AS (SELECT * FROM 
      ((SELECT
          src.value AS 'sr_code',
          DATE(rd.value) AS 'release_date',
          sw.value AS 'songwriter',
          SEC_TO_TIME(rt.value) AS 'runtime',
          COALESCE(track.original, 0) AS 'original',
          COALESCE(track.exclude_mlc, 0) AS 'exclude_mlc'
      FROM catalog_product_entity prod
      LEFT JOIN catalog_product_entity_datetime rd ON rd.row_id = prod.row_id AND rd.store_id = 0 AND rd.attribute_id = 249
      LEFT JOIN catalog_product_entity_varchar src ON src.row_id = prod.row_id AND src.store_id = 0 AND src.attribute_id = 218
      LEFT JOIN catalog_product_entity_varchar sw ON sw.row_id = prod.row_id AND sw.store_id = 0 AND sw.attribute_id = 308
      LEFT JOIN catalog_product_entity_int rt ON rt.row_id = prod.row_id AND rt.store_id = 0 AND rt.attribute_id = 226
      LEFT JOIN downloadable_link link ON prod.row_id = link.product_id
      LEFT JOIN downloadable_link_track track ON track.link_id = link.link_id 
      WHERE prod.attribute_set_id = 13 AND src.value IS NOT NULL AND track.original = 0 AND track.exclude_mlc = 0)
      UNION ALL
      (SELECT
          track.sr_code AS 'sr_code',
          DATE(rd.value) AS 'release_date',
          track.songwriter AS 'songwriter',
          SEC_TO_TIME(track.runtime) AS 'runtime',
          COALESCE(track.original, 0) AS 'original',
          COALESCE(track.exclude_mlc, 0) AS 'exclude_mlc'
      FROM catalog_product_entity prod
      LEFT JOIN catalog_product_entity_datetime rd ON rd.row_id = prod.row_id AND rd.store_id = 0 AND rd.attribute_id = 249
      LEFT JOIN downloadable_link link ON link.product_id = prod.row_id
      LEFT JOIN downloadable_link_track track ON track.link_id = link.link_id
      LEFT JOIN downloadable_link_title title ON title.link_id = link.link_id
      WHERE prod.attribute_set_id = 12 AND track.sr_code IS NOT NULL AND track.original = 0 AND track.exclude_mlc = 0))
    AS 'temp_data_table');
    

    But when I run the query, I'm returned with a syntax error. At first, I was just running the union selects as the create statement, which I learned you cannot do. I wrapped it in another select to use that statement as a subquery (as indicated here - https://stackoverflow.com/a/31751787/18582667 ). I'm able to get the SELECT + UNION + SELECT to run without the temp table syntax so my query itself seems to be fine.

    This is what I receive: 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 ''temp_data_table')' at line 31 I'm not sure what I did wrong and why it's throwing that at me. I'm leaning towards parens, but I don't have any non-paired ones in there.

    I am working with a Magento 2 install, but I don't believe that's entirely relevant. Throwing it in there just to be thorough.

    Running MySQL 5.6.42-84.2 Percona Server (GPL), Release 84.2, Revision 6b2b987 on Red Hat Enterprise Linux Server 7.9 (Maipo).



  • Aliasing a table or subquery with a quoted string will error.

    DROP TABLE IF EXISTS temp_data;
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_data AS (SELECT * FROM 
      ((SELECT
          src.value AS 'sr_code',
          DATE(rd.value) AS 'release_date',
          sw.value AS 'songwriter',
          SEC_TO_TIME(rt.value) AS 'runtime',
          COALESCE(track.original, 0) AS 'original',
          COALESCE(track.exclude_mlc, 0) AS 'exclude_mlc'
      FROM catalog_product_entity prod
      LEFT JOIN catalog_product_entity_datetime rd ON rd.row_id = prod.row_id AND rd.store_id = 0 AND rd.attribute_id = 249
      LEFT JOIN catalog_product_entity_varchar src ON src.row_id = prod.row_id AND src.store_id = 0 AND src.attribute_id = 218
      LEFT JOIN catalog_product_entity_varchar sw ON sw.row_id = prod.row_id AND sw.store_id = 0 AND sw.attribute_id = 308
      LEFT JOIN catalog_product_entity_int rt ON rt.row_id = prod.row_id AND rt.store_id = 0 AND rt.attribute_id = 226
      LEFT JOIN downloadable_link link ON prod.row_id = link.product_id
      LEFT JOIN downloadable_link_track track ON track.link_id = link.link_id 
      WHERE prod.attribute_set_id = 13 AND src.value IS NOT NULL AND track.original = 0 AND track.exclude_mlc = 0)
      UNION ALL
      (SELECT
          track.sr_code AS 'sr_code',
          DATE(rd.value) AS 'release_date',
          track.songwriter AS 'songwriter',
          SEC_TO_TIME(track.runtime) AS 'runtime',
          COALESCE(track.original, 0) AS 'original',
          COALESCE(track.exclude_mlc, 0) AS 'exclude_mlc'
      FROM catalog_product_entity prod
      LEFT JOIN catalog_product_entity_datetime rd ON rd.row_id = prod.row_id AND rd.store_id = 0 AND rd.attribute_id = 249
      LEFT JOIN downloadable_link link ON link.product_id = prod.row_id
      LEFT JOIN downloadable_link_track track ON track.link_id = link.link_id
      LEFT JOIN downloadable_link_title title ON title.link_id = link.link_id
      WHERE prod.attribute_set_id = 12 AND track.sr_code IS NOT NULL AND track.original = 0 AND track.exclude_mlc = 0))
    AS temp_data_table);
    

    See this demo https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=888a778cf10ce741efca9503d312691a




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2