Syntax error Using CREATE TABLE AS statement with UNION of two tables in MySQL

Using CREATE TABLE AS statement with UNION of two tables in MySQL

For this, you can use UNION. Let us first create a table −

mysql> create table DemoTable1(FirstName varchar(1000));
Query OK, 0 rows affected (1.22 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1 values('John');
Query OK, 1 row affected (0.20 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable1;

This will produce the following output −

+-----------+
| FirstName |
+-----------+
| John      |
+-----------+
1 row in set (0.02 sec)

Here is the query to create second the table −

mysql> create table DemoTable2(FirstName varchar(100));
Query OK, 0 rows affected (0.81 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable2 values('Chris');
Query OK, 1 row affected (0.21 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable2;

This will produce the following output −

+-----------+
| FirstName |
+-----------+
| Chris    |
+-----------+
1 row in set (0.00 sec)

Following is the query for CREATE TABLE AS statement and displaying the union of two or more tables in it −

mysql> create table DemoTable3 AS
   (
      select FirstName, 'DemoTable1' AS `TABLE_NAME` from DemoTable1)
      union
   (
   select FirstName, 'DemoTable2' AS `TABLE_NAME` from DemoTable2);
Query OK, 2 rows affected (1.08 sec)
Records: 2 Duplicates: 0 Warnings: 0

Display all the records from the table DemoTable3 −

mysql> select *from DemoTable3;

This will produce the following output −

+-----------+--------------+
| FirstName | TABLE_NAME   |
+-----------+--------------+
| John      | DemoTable1   |
| Chris     | DemoTable2   |
+-----------+--------------+
2 rows in set (0.00 sec)
Updated on: 2019-08-22T10:54:03+05:30

2K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements