Create a new table with the properties of an old table and without duplicates using MySQL LIKE Operator?

MySQLMySQLi Database

To achieve this with LIKE operator, the following is the syntax −

CREATE TABLE yourTableName2 LIKE yourTableName1;

To understand the syntax, let us create a table and insert some records into it. The following is the query to create a table −

mysql> create table Employee
−> (
   −> EmployeeId int
   −> ,
   −> EmployeeName varchar(100)
−> );
Query OK, 0 rows affected (0.54 sec)

Inserting records into the table with the help of insert command. The query is as follows −

mysql> insert into Employee values(1,'Carol');
Query OK, 1 row affected (0.18 sec)

mysql> insert into Employee values(2,'John');
Query OK, 1 row affected (0.16 sec)

mysql> insert into Employee values(3,'Johnson');
Query OK, 1 row affected (0.11 sec)

Displaying all records with the help of select statement. The query to display all records are as follows −

mysql> select *from StudentTable;

The following is the output −

+------+---------+
| Id   |    Name |
+------+---------+
| 3    | Johnson |
+------+---------+
1 row in set (0.00 sec)

Now you can implement the above syntax which I discussed in the beginning. The query is as follows to create a table with the help of LIKE operator.

mysql> create table StudentModifyTableDemo Like Employee;
Query OK, 0 rows affected (0.50 sec)

You can check the definition of the new table with the help of desc command. The query is as follows −

mysql> desc StudentModifyTableDemo;

The following is the output −

+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| EmployeeId   | int(11)      | YES  |     | NULL    |       |
| EmployeeName | varchar(100) | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
2 rows in set (0.12 sec)

Now you can insert all records of Employee table to StudentModifyTableDemo table.

The query is as follows −

mysql> insert into StudentModifyTableDemo select *from Employee;
Query OK, 3 rows affected (0.16 sec)
Records: 3 Duplicates: 0 Warnings: 0

Now you can check all records are present in the second table or not. The query is as follows −

mysql> select *from StudentModifyTableDemo;

The following is the output −

+------------+--------------+
| EmployeeId | EmployeeName |
+------------+--------------+
|          1 |        Carol |
|          2 |         John |
|          3 |      Johnson |
+------------+--------------+
3 rows in set (0.00 sec)
raja
Published on 10-Jan-2019 11:19:29
Advertisements