postgre将旧SQL表中的数据插入到新表中

Question

我有以下Postgres表

userstable:

id  |
-----|

user_follows表:

follower_id | following_id
------------|--------------

communitiestable (new table):

id  | owner_id
-----|-----------

community_memberstable (new table):

community_id | member_id
-------------|------------

communities已经存在，每个owner_id都有一个。我试图构建一个SQL语句，将数据从user_follows移动到community_members给定社区所有者的id。following_id应该是owner_id,follower_id应该是member_id

follower_id、following_id、owner_id、member_id都是对users表id的引用。

谢谢!

编辑:这是我目前的想法

INSERT INTO community_members (community_id, member_id)
SELECT communities.id, user_follows.follower_id
FROM communities JOIN user_follows
WHERE communities.owner_id = 123
AND user_follows.following_id = 123;
``

Answer 1

我明白了。工作查询是

INSERT INTO community_members (community_id, member_id)
SELECT communities.id, user_follows.follower_id
FROM communities JOIN user_follows
ON communities.owner_id = user_follows.following_id

如果能够为每个用户自动执行此操作，而不是为每个用户运行查询，将是很酷的。现在这是ok的，因为有<15个用户，我需要为

运行这个edit:已更新为所有社区所有者运行