Copy Column Values from One Table to Another Matching IDs in MySQL

Let us first create a table −

mysql> create table DemoTable1
(
   PersonId int,
   Value int
);
Query OK, 0 rows affected (0.64 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1 values(100,78);
Query OK, 1 row affected (0.46 sec)
mysql> insert into DemoTable1 values(101,67);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable1 values(102,89);
Query OK, 1 row affected (0.19 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable1;

This will produce the following output −

+----------+-------+
| PersonId | Value |
+----------+-------+
|      100 |    78 |
|      101 |    67 |
|      102 |    89 |
+----------+-------+
3 rows in set (0.00 sec)

Following is the query to create the second table.

mysql> create table DemoTable2
(
   StudentId int,
   StudentScore int
);
Query OK, 0 rows affected (0.57 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable2 values(100,NULL) ;
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable2 values(102,NULL);
Query OK, 1 row affected (0.22 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable2;

This will produce the following output −

+-----------+--------------+
| StudentId | StudentScore |
+-----------+--------------+
|       100 |         NULL |
|       102 |         NULL |
+-----------+--------------+
2 rows in set (0.00 sec)

Following is the query to copy column value from one table into another matching ids −

mysql> update DemoTable1, DemoTable2 set DemoTable2.StudentScore = DemoTable1.Value where DemoTable2.StudentId=DemoTable1.PersonId;
Query OK, 2 rows affected (0.13 sec)
Rows matched: 2 Changed: 2 Warnings: 0

Let us check the table records once again −

mysql> select *from DemoTable2;

This will produce the following output −

+-----------+--------------+
| StudentId | StudentScore |
+-----------+--------------+
|       100 |           78 |
|       102 |           89 |
+-----------+--------------+
2 rows in set (0.00 sec)