creating new table from multiple tables

user_16110118 0
Viewed 83

Hi I'm trying to create a new lookup tables in which I need all columns that have date values from all the tables in a particular schema. When I'm trying to UNION the column, I am getting only the result of 1st select and seems like union is not working here. How can I get all these columns in my new table? Here's my code for reference:

CREATE TABLE datetime_test AS (
SELECT col1, null as col2, null as col3 from table1
union all 
select col1, col2, col3 from table2
union all 
select col1, col2, null as col3 from table3 );
mysql
1 Answers

Error: Invalid operation: UNION types timestamp without time zone and integer cannot be matched

You must cast NULL in upper subquery of UNION to proper datatype. I.e. something like

SELECT col1, CAST(null AS DATETIME) as col2, CAST(null AS CHAR) as col3 from table1
UNION ALL
...

I've changed my question a bit. I am getting the result from only 1st select i.e. columns from table1 in new table. Could you help me in understanding what is wrong here, or how can I get all columns? – Ros

CREATE TABLE .. SELECT needs in "scalar" query as a datasource. So use

CREATE TABLE datetime_test 
AS
SELECT *
FROM ( SELECT col1, null as col2, null as col3 from table1
       union all 
       select col1, col2, col3 from table2
       union all 
       select col1, col2, null as col3 from table3 ) AS total_data;

PS. I recommend you to define precise columns datatypes in CREATE TABLE part. Additionally this will make the casting for NULLs in UNION needless.

user_101387340
Related