mysql insert union_无法在INSERT MySQL中使用UNION？

I have a select query that works fine. But when I wrap it into an INSERT statement, I get an error.

Error 1064 - 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 DISTINCT NULL AS id,NULL AS core_value_id,NULL AS translation_id' at line 3

I can solve this problem by using 1 insert for each source table. But is there any way to do it with a single query? Why isn't this working?

Here's the query.

INSERT INTO `some_table`

(

(SELECT DISTINCT

NULL AS `id`,

NULL AS `core_value_id`,

NULL AS `translation_id`,

t1.`upc` AS `source_value`,

t1.`upc` AS `value`,

COUNT(*) AS `count`

FROM `source_table_1` t1

GROUP BY `upc`)

UNION ALL

(SELECT DISTINCT

NULL,NULL,NULL,

t1.`upc`,

t1.`upc`,

COUNT(*) AS `count`

FROM `source_table_2` t1

GROUP BY `upc`

)

ORDER BY `count` DESC

)

Here's the table definition:

CREATE TABLE `some_table` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`core_value_id` int(11) DEFAULT NULL,

`translation_id` int(11) DEFAULT NULL,

`source_value` varchar(255) NOT NULL,

`value` varchar(255) DEFAULT NULL,

`count` int(11) NOT NULL,

PRIMARY KEY (`id`),

KEY `core_value_id` (`core_value_id`),

KEY `translation_id` (`translation_id`),

KEY `source_value` (`source_value`),

KEY `value` (`value`),

KEY `count` (`count`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

source_tables would look something like this:

CREATE TABLE `source_table_1` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`upc` bigint(20) DEFAULT NULL,

PRIMARY KEY (`id`),

) ENGINE=InnoDB DEFAULT CHARSET=utf8

The upc columns would have data like:

123456789012

123456789013

123456789014

123456789015

123456789016

123456789017

解决方案

My guess is you are missing a SELECT clause on top of the individual selects. Furthermore the parentheses were not appropriate. Try this instead:

INSERT INTO `some_table`

SELECT *

FROM

(

SELECT DISTINCT NULL AS `id`, NULL AS `core_value_id`,

NULL AS `translation_id`, t1.`upc` AS `source_value`,

t1.`upc` AS `value`, COUNT(*) AS `count`

FROM `source_table_1` t1

GROUP BY `upc`

UNION ALL

SELECT DISTINCT NULL, NULL, NULL, t1.`upc`, t1.`upc`,

COUNT(*) AS `count`

FROM `source_table_2` t1

GROUP BY `upc`

) AS dt

ORDER BY `count` DESC