将一张表中的列值复制到另一张表中，匹配MySQL中的 IDs

首先，让我们创建一张表 –

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

使用插入命令向表中插入一些记录 –

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)

使用 select 语句显示表中的所有记录 –

mysql> select *from DemoTable1;

这将产生以下输出 –

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

以下是创建第二个表的查询。

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

使用插入命令向表中插入一些记录 –

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)

使用 select 语句显示表中的所有记录 –

mysql> select *from DemoTable2;

这将产生以下输出 –

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

以下是将一张表中的列值复制到另一张表中匹配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

让我们再次检查表记录 –

mysql> select *from DemoTable2;

这将产生以下输出 –

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