CODEWITHSUNDEEP
mysql
sergeda
sergeda

Reputation: 2201

How to merge two tables without id and with different number of columns in MySQL

I have two tables with different columns. Tables doesn't have id column. They have the same number of rows. I want to merge them in new table. I've tried to do this like this:

CREATE TABLE test_3_cut_dest as 
 SELECT * FROM test_3_cut_ 
  UNION 
 SELECT * FROM test_3_cut

but got error:

each UNION query must have the same number of columns

I want to know how achieve merging of two tables with different number of columns without specifying list of columns?

Upvotes: 0

Views: 916

Answers (4)

FDavidov
FDavidov

Reputation: 3675

Union requires that all participating selects yield matching record sets, meaning same number of columns and compatible data types for each column.

As for your question, you can use placeholders wherever there is a mismatch. For instance:

SELECT Col1, Col2, 'DUMMYVALUE'
  FROM Table1
UNION ALL
SELECT Col1, Col2, Col3
  FROM Table2 ;

Now, if table1 has 3 textual columns and table2 has 6 textual columns, you can go with:

SELECT * , 'DUMMYVALUE1','DUMMYVALUE2','DUMMYVALUE3'
  FROM Table1
UNION ALL
SELECT *
  FROM Table2 ;

Upvotes: 0

Bitong
Bitong

Reputation: 36

I don't think it works with select * when it comes to not knowing how many columns each table has. The best way to do it is like this:

SELECT A, B, C, D, E, F, G, H FROM test_3_cut_
UNION
SELECT A, B, NULL AS C, NULL AS D, NULL AS E, NULL AS ...  FROM test_3_cut

I've done it like this considering test_3_cut has fewer columns than test_3_cut_

Upvotes: 1

Stefano Zanini
Stefano Zanini

Reputation: 5916

You have to select from the two tables the same set of columns. If one of the tables has more columns, you can either select only the columns they share or select a fake value for the missing column from the table that has less.

Example

Table1
col1 | col2 | col3

Table2
col1 | col2

You can do this

select col1, col2 from Table1
union all
select col1, col2 from Table2

or this

select col1, col2, col3 from Table1
union all
select col1, col2, '' from Table2

Upvotes: 0

ARr0w
ARr0w

Reputation: 1731

Union works like this:

Select column1 from table1
union
select column1 from table2

Number of columns must be same in both Select Queries.

Upvotes: 0

Related Questions