Having problems creating temporary table from UNION
I have a UNION statement that runs just fine:
SELECT "1999999999" AS MobileNo, "Test" AS FirstName, "Last" AS LastName, "268" AS TemplateID, "" AS MISC1, "" AS MISC2 UNION SELECT cust.cellp AS MobileNo, acct.firstname AS FirstName, acct.lastname AS LastName, "268" AS TemplateID, "" AS MISC1, "" AS MISC2 FROM acct INNER JOIN cust ON (cust.socsec=acct.socsec)
However, when I try to collapse it with CREATE TEMPORARY TABLE:
CREATE TEMPORARY TABLE temptable (SELECT "1999999999" AS MobileNo, "Test" AS FirstName, "Last" AS LastName, "268" AS TemplateID, "" AS MISC1, "" AS MISC2 UNION SELECT cust.cellp AS MobileNo, acct.firstname AS FirstName, acct.lastname AS LastName, "268" AS TemplateID, "" AS MISC1, "" AS MISC2 FROM acct INNER JOIN cust ON (cust.socsec=acct.socsec))
I am getting this error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual corresponding to your MySQL server version for the correct syntax to use next to UNION SELECT cust.cellp AS MobileNo, acct.firstname AS FirstName, ac 'on line 1
Any SELECT statement works fine with a CREATE TEMPORARY TABLE around it, it's just UNION which doesn't work. The MySQL error returned is undefined, so I can't figure out what the problem is. I tried to wrap each SELECT statement in parentheses, but didn't like that either.
I know that in theory I could just create a temp table with one SELECT and then add the data from the second to the table, but this solution is not realistic in this case since this is the reporter that I use it pretty strictly as I I only allow one MySQL expression as input, so unless I want to redesign the whole system (I don't want the manual not wanting me to waste time on this right now), I need to find a way to make it work in a single MySQL statement ...
Any ideas on what I am doing wrong here?
source to share
Here's a workaround:
CREATE TABLE AS
SELECT *
FROM (
SELECT ...
UNION ALL
SELECT ...
) AS foo
You cannot do the union directly for create table
, but you can subselect it:
mysql> create table foo as (select * from ((select 'foo') union all (select 'bar')) as foo);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
source to share