MySQL query for INSERT INTO using values from another table?
For this, use INSERT INTO SELECT statement.
Let us create a table −
Example
mysql> create table demo82
-> (
-> id int,
-> name varchar(20)
-> );
Query OK, 0 rows affected (2.06
Insert some records into the table with the help of insert command −
Example
mysql> insert into demo82 values(100,'John');
Query OK, 1 row affected (0.14
mysql> insert into demo82 values(101,'Bob');
Query OK, 1 row affected (0.32
mysql> insert into demo82 values(101,'David');
Query OK, 1 row affected (0.09
mysql> insert into demo82 values(101,'Mike');
Query OK, 1 row affected (0.12
mysql> insert into demo82 values(100,'Sam');
Query OK, 1 row affected (0.07
Display records from the table using select statement −
Example
mysql> select *from demo82;
This will produce the following output −
Output
+------+-------+
| id | name |
+------+-------+
| 100 | John |
| 101 | Bob |
| 101 | David |
| 101 | Mike |
| 100 | Sam |
+------+-------+
5 rows in set (0.00 sec)
Following is the query to create second table.
Example
mysql> create table demo83
-> (
-> id int,
-> username varchar(20)
-> );
Query OK, 0 rows affected (1.25
Insert some records into the second table with the help of insert command −
Example
mysql> insert into demo83(id,username)
-> select id,name from demo82 where id=101;
Query OK, 3 rows affected (0.14 sec)
Records: 3 Duplicates: 0 Warnings: 0
Display records from the second table using select statement −
Example
mysql> select *from demo83;
This will produce the following output −
Output
+------+----------+
| id | username |
+------+----------+
| 101 | Bob |
| 101 | David |
| 101 | Mike |
+------+----------+
3 rows in set (0.00 sec)
Published on 11-Dec-2020 10:11:54
