Can we implement nested insert with select in MySQL?
Yes, we can implement nested insert with select in MySQL as shown in the below syntax −
insert into yourTableName2(yourColumnName1,yourColumnName2,.....N)
select yourColumnName1,yourColumnName2,....N from yourTableName1 where yourCondition;
Let us first see an example and create a table −
mysql> create table DemoTable1
(
Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name varchar(40)
);
Query OK, 0 rows affected (0.88 sec)
Insert some records in the table using insert command −
mysql> insert into DemoTable1(Name) values('Chris');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable1(Name) values('David');
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable1(Name) values('Bob');
Query OK, 1 row affected (0.12 sec)Display all records from the table using select statement −
mysql> select *from DemoTable1;
This will produce the following output −
+----+-------+
| Id | Name |
+----+-------+
| 1 | Chris |
| 2 | David |
| 3 | Bob |
+----+-------+
3 rows in set (0.00 sec)
Following is the query to create second table −
mysql> create table DemoTable1
(
ClientId int,
ClientFirstName varchar(20)
);
Query OK, 0 rows affected (0.60 sec)
Following is the query to implement nested insert with select −
mysql> insert into DemoTable2(ClientId,ClientFirstName)
select Id,Name from DemoTable1 where Id !=2;
Query OK, 2 rows affected (0.17 sec)
Records : 2 Duplicates: 0 Warnings : 0
Display all records from the table using select statement −
mysql> select *from DemoTable2;
This will produce the following output −
+----------+-----------------+
| ClientId | ClientFirstName |
+----------+-----------------+
| 1 | Chris |
| 3 | Bob |
+----------+-----------------+
2 rows in set (0.00 sec)
Published on 10-Oct-2019 15:07:51
