0

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

2 Answers 2

0

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

0

I don't think you need the outer SELECT *. Instead simply use parens:

CREATE ... AS
    (
      ( SELECT ... )
      UNION ALL
      ( SELECT ... )
    );

If you were running MySQL 8.0, a WITH clause might be useful.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.