将一张表中的列值复制到另一张表中,匹配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)
阅读更多:MySQL 教程