使用子查询和连接更新PostgreSQL

我的PostgreSQL数据库中有以下表:

1. 建筑

 id |           name           | abbreviation 
----+--------------------------+--------------
 31 | 4705 Fifth Avenue - Dept | 4705FIFTH-D
 28 | 4705 Fifth Avenue        | 4705FIFTH
...

2. 建筑_网络

 id  | 建筑_id | 网络_id 
-----+--------------+-------------
 143 |           31 |         159
 144 |           31 |         160
 147 |           28 |         153
 148 |           28 |         154
 149 |           28 |         155
 159 |           31 |         179
...

3. 网络

 id  |          name          | display_name 
-----+------------------------+--------------------
 179 | 4705FIFTH-D -- fmcs    | fmcs (staff)
 153 | 4705FIFTH -- onboard   | onboard (residents)
 154 | 4705FIFTH -- private   | private (residents)
 155 | 4705FIFTH -- public    | public (residents)
 159 | 4705FIFTH-D -- onboard | onboard (staff)
 160 | 4705FIFTH-D -- private | private (staff
...

我正在try 更新建筑_网络表，以便所有网络(包括名称中包含-D的网络)都与名称中没有- Dept的类似建筑相关联.

使用上面的例子，建筑_网络表理想情况下应该是这样的:

 id  | 建筑_id | 网络_id 
-----+--------------+-------------
 143 |           28 |         159
 144 |           28 |         160
 147 |           28 |         153
 148 |           28 |         154
 149 |           28 |         155
 159 |           28 |         179
...

我意识到这可以在示例中逐行完成，但除了我在本示例中提供的建筑物和网络之外，还有许多额外的建筑物和网络. 因此，单个查询可以节省很多时间.

其他建筑物各不相同，但每个建筑物总是有两个. 第一个子集是名称末尾有- Dept的子集，例如4705 Fifth Avenue - Dept. -Dept建筑物总是有一个以-D结尾的缩写，比如4705FIFTH-D. 建筑物的第二个子集的名称和缩写分别没有- Dept和-D、4705 Fifth Avenue和4705FIFTH.

此外，总有两种网络名称. 其中一个子集是名称第一部分末尾有-D的那些，例如4705FIFTH-D -- onboard. 另一个子集没有-D，例如4705FIFTH -- onboard.

我try 过这个:

UPDATE 建筑_网络 bn
SET 建筑_id = subquery2.building_id
FROM (
  SELECT b2.id AS building_id, b2.name, b2.abbreviation, bn2.网络_id
  FROM 建筑 b2
  INNER JOIN 建筑_网络 bn2
  ON bn2.建筑_id = b2.id
  WHERE b2.abbreviation NOT LIKE '%-D'
) AS subquery2
INNER JOIN 建筑 b
ON b.id = subquery2.building_id
WHERE replace(b.abbreviation, '-D', '') LIKE subquery2.abbreviation
OR b.abbreviation LIKE subquery2.abbreviation;

这看起来很接近，但它将建筑_id个值中的所有值设置为单个值(例如，除了本例中所示的记录之外，还有400多条记录)，而不是每个建筑物.

此查询会产生相同(不期望的)结果:

UPDATE 建筑_网络
SET 建筑_id = (
  SELECT b2.id 
  FROM 建筑 b2 
  WHERE b2.abbreviation = REPLACE(b.abbreviation, '-D', '')
)
FROM 建筑_网络 bn
INNER JOIN 建筑 b 
ON b.id = bn.建筑_id
WHERE b.abbreviation LIKE '%-D';

我哪里错了？