需求

表1

table: h1

id  name
1	hello1
2	hello2
3	hello11
4	hello22

表2

table: h2

h1_id  name
1
2
1
3
3
2
4

想要的结果

h1_id name
1	hello1
2	hello2
1	hello1
3	hello11
3	hello11
2	hello2
4	hello22

SQL

MySQL

UPDATE h2 INNER JOIN h1 on h1.id = h2.h1_id set h2.name = h1.name;

PostgreSQL

update h2 set name = h1.name from h1 where h1.id = h1_id;

注意，在PostgreSQL里，不能写成

update h2 set h2.name = h1.name from h1 where h1.id = h2.h1_id;

对于h2的列，要直接写成name，而不是h2.name，否则就会报如下错误:

postgres=# update h2 set h2.name = h1.name from h1 where h1.id = h2.h1_id;
ERROR:  column "h2" of relation "h2" does not exist
LINE 1: update h2 set h2.name = h1.name from h1 where h1.id = h2.h1_...
                      ^
postgres=#

注意事项

如果是想update全表，并且数据量非常大的话，可以采取分而治之的方法。比如：一次update 1000条，如此下去。比如：

MySQL

UPDATE h2 INNER JOIN h1 on h1.id = h2.h1_id set h2.name = h1.name where h2.h1_id >=0 and  h2.h1_id <= 1000;

PostgreSQL

update h2 set name = h1.name from h1 where h1.id = h1_id and h1_id >=0 and h1_id <= 1000;