lamda postgresql mysql 联合查询_MySQL与PostgreSQL的简单表关联更新

现有两个测试表，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;