Syntax error How to SELECT fields from one table and INSERT to another in MySQL?

How to SELECT fields from one table and INSERT to another in MySQL?

Let us first create a table −

mysql> create table DemoTable1
   -> (
   -> StudentId int,
   -> StudentName varchar(20)
   -> );
Query OK, 0 rows affected (0.64 sec)

Insert some records in the table using insert command −

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

mysql> insert into DemoTable1 values(11,'Chris');
Query OK, 1 row affected (0.15 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable1;

Output

+-----------+-------------+
| StudentId | StudentName |
+-----------+-------------+
| 10        | John        |
| 11        | Chris       |
+-----------+-------------+
2 rows in set (0.00 sec)

Following is the query to create second table −

mysql> create table DemoTable2
   -> (
   -> CustomerId int,
   -> CustomerName varchar(100)
   -> );
Query OK, 0 rows affected (0.82 sec)

Here is the query to SELECT fields from one table and INSERT to another −

mysql> insert into DemoTable2(CustomerId,CustomerName) select StudentId,StudentName from DemoTable1 where StudentId=11;
Query OK, 1 row affected (0.20 sec)
Records: 1 Duplicates: 0 Warnings: 0

Let us check all the records from the second table again −

mysql> select *from DemoTable2;

Output

+------------+--------------+
| CustomerId | CustomerName |
+------------+--------------+
| 11         | Chris        |
+------------+--------------+
1 row in set (0.00 sec)
Updated on: 2019-07-30T22:30:26+05:30

205 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements