使用子查询与连接更新PostgreSQL表的错误排查求助

我的PostgreSQL数据库包含以下三张表：

1. buildings表：

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

2. buildings_networks表：

id  | buildings_id | networks_id 
-----+--------------+-------------
 143 |           31 |         159
 144 |           31 |         160
 147 |           28 |         153
 148 |           28 |         154
 149 |           28 |         155
 159 |           31 |         179
...

3. networks表：

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)
...

我需要更新buildings_networks表，让所有网络（包括名称含-D的）都关联到名称不含- Dept的对应建筑，理想结果如下：

id  | buildings_id | networks_id 
-----+--------------+-------------
 143 |           28 |         159
 144 |           28 |         160
 147 |           28 |         153
 148 |           28 |         154
 149 |           28 |         155
 159 |           28 |         179
...

所有建筑都成对存在：一类名称以- Dept结尾，缩写以-D结尾；另一类无该后缀。网络名称也分含-D和不含-D两类。

我尝试了以下两条SQL语句，但都把所有buildings_id设置成了同一个值，没有按建筑分别更新，请问哪里出错了？

语句1：

UPDATE buildings_networks bn
SET buildings_id = subquery2.building_id
FROM (
  SELECT b2.id AS building_id, b2.name, b2.abbreviation, bn2.networks_id
  FROM buildings b2
  INNER JOIN buildings_networks bn2
  ON bn2.buildings_id = b2.id
  WHERE b2.abbreviation NOT LIKE '%-D'
) AS subquery2
INNER JOIN buildings b
ON b.id = subquery2.building_id
WHERE replace(b.abbreviation, '-D', '') LIKE subquery2.abbreviation
OR b.abbreviation LIKE subquery2.abbreviation;

语句2：

UPDATE buildings_networks
SET buildings_id = (
  SELECT b2.id 
  FROM buildings b2 
  WHERE b2.abbreviation = REPLACE(b.abbreviation, '-D', '')
)
FROM buildings_networks bn
INNER JOIN buildings b 
ON b.id = bn.buildings_id
WHERE b.abbreviation LIKE '%-D';

语句1的问题 #

子查询subquery2关联了buildings_networks，返回多条记录，但外层UPDATE没有建立bn和subquery2的关联条件，PostgreSQL会随机匹配一条记录的building_id赋值给所有符合WHERE条件的行，最终所有行被设为同一个值。

语句2的问题 #

子查询中的REPLACE(b.abbreviation, '-D', '')里的b是外层FROM的buildings表，但该子查询未与当前要更新的buildings_networks行关联。当存在多个符合b2.abbreviation = REPLACE(b.abbreviation, '-D', '')的记录时，PostgreSQL会取第一条的id，导致所有行被设为同一个值。此外，这条语句只处理了关联到带-D缩写建筑的网络，未覆盖原本关联到非-D建筑的网络（虽然需求中这些不需要修改，但逻辑不完整）。

正确的SQL语句 #

我们需要为每一条buildings_networks记录匹配对应的目标建筑：

UPDATE buildings_networks bn
SET buildings_id = target_building.id
FROM buildings current_building
JOIN buildings target_building 
  ON target_building.abbreviation = REPLACE(current_building.abbreviation, '-D', '')
WHERE current_building.id = bn.buildings_id;

逻辑说明 #

• 通过current_building.id = bn.buildings_id关联buildings_networks和当前建筑，确保每一行对应正确的当前建筑
• 利用REPLACE(current_building.abbreviation, '-D', '')找到配对的目标建筑
• 将buildings_id更新为目标建筑的id

这条语句会处理所有buildings_networks记录：

• 若当前关联的是带-D的建筑，会更新到对应的不带-D的建筑
• 若当前关联的已是不带-D的建筑，REPLACE后缩写不变，匹配到自身，相当于不修改（符合需求）

若要验证逻辑，可先运行以下查询查看结果：

SELECT 
  bn.id, 
  current_building.id AS original_building_id, 
  target_building.id AS new_building_id, 
  bn.networks_id
FROM buildings_networks bn
JOIN buildings current_building ON current_building.id = bn.buildings_id
JOIN buildings target_building ON target_building.abbreviation = REPLACE(current_building.abbreviation, '-D', '');

内容的提问来源于stack exchange，提问作者mgermaine93