MySQL与PostgreSQL的简单表关联更新

2019独角兽企业重金招聘Python工程师标准>>>

现有两个测试表，user和student，数据如下

mysql> select * from user;
+----+------+--------+-----+---------------------+
| id | name | gender | age | updated_time        |
+----+------+--------+-----+---------------------+
|  1 | 张三     | M      |  18 | 2018-11-05 10:50:40 |
|  2 | 李四     | M      |  17 | 2018-11-05 10:51:28 |
|  3 | 王五     | W      |  18 | 2018-11-05 10:51:44 |
+----+------+--------+-----+---------------------+
3 rows in set (0.00 sec)

mysql> select * from student;
+----+------+------+------+-----------+---------------------+
| id | name | sex  | age  | school    | updated_time        |
+----+------+------+------+-----------+---------------------+
|  1 | 张三     | NULL | NULL | 高二(1)班      | 2018-11-05 10:55:12 |
|  2 | 李四     | NULL | NULL | 高二(3)班      | 2018-11-05 10:55:28 |
|  3 | 王五     | NULL | NULL | 高二(1)班      | 2018-11-05 10:55:39 |
+----+------+------+------+-----------+---------------------+
3 rows in set (0.00 sec)

将user中的age和gender更新到student中的age和sex上

mysql> update student s,user u set s.sex=u.gender,s.age=u.age where s.name=u.name;
Query OK, 3 rows affected (0.03 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from student;
+----+------+------+------+-----------+---------------------+
| id | name | sex  | age  | school    | updated_time        |
+----+------+------+------+-----------+---------------------+
|  1 | 张三     | M    |   18 | 高二(1)班      | 2018-11-05 11:21:23 |
|  2 | 李四     | M    |   17 | 高二(3)班      | 2018-11-05 11:21:23 |
|  3 | 王五     | W    |   18 | 高二(1)班      | 2018-11-05 11:21:23 |
+----+------+------+------+-----------+---------------------+
3 rows in set (0.00 sec)

现在PostgreSQL中有是相同的表，相同的数据，相同的需求，更新语句如下

update student set age=u.age,sex=u.gender from "user" u where student.id=u.id;

 

转载于:https://my.oschina.net/lixuelong/blog/2870137