CODEWITHSUNDEEP
mysqlunion
Arman
Arman

Reputation: 4626

MYSQl Query with union is failing

I have a two tables A and B I would like to union them and store in to another table;

CREATE TABLE  myspace.test (
(select * from A ) UNION ( select * from B) );

It fails with an error

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 'CREATE TABLE myspace.test ( (select * from A ) UNION ( s' at line 1

But the query with: (select * from A ) UNION ( select * from B) gives correct result.

How to store union result in to another table??

Thanks Arman.

EDIT

Well After playing around I found that:

Upvotes: 4

Views: 2674

Answers (3)

Lightness Races in Orbit
Lightness Races in Orbit

Reputation: 385104

Read the documentation. There are no parens around the select-statement.

CREATE TABLE `myspace`.`test` (SELECT * FROM `A`) UNION (SELECT * FROM `B`);

Watch out for duplicate primary keys, though. You may want to consider first creating an empty table myspace.test with the proper layout, then inserting rows into it more selectively.

Upvotes: 2

Skorpioh
Skorpioh

Reputation: 1355

This does not look right at first sight.

Start by simply creating your new empty table using CREATE TABLE.

Then run a query to populate it, should be something like 

INSERT INTO newTable(field1, field2,..., fieldN)
SELECT temp.field1, temp.field2,...,temp.fieldN
FROM
(
    SELECT field1, field2,...,fieldN
    FROM A
    UNION
    SELECT field1, field2,...,fieldN
    FROM B
) temp

Hope this helps!

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425291

CREATE TABLE
        myspace.test
AS
SELECT  *
FROM    A
UNION
SELECT  *
FROM    B

Upvotes: 4

Related Questions