自连接表更新报错：UPDATE/MERGE每行匹配源行超限制求助

错误原因 #

你的更新查询触发该错误，是因为单个需要更新的行（housing_1中PropertyAddress为空的记录）匹配到了多个同ParcelID的housing_2行。BigQuery不允许这种一对多的更新操作——它无法确定应该用哪一行的地址来覆盖目标值。

修复方案 #

通过子查询对匹配结果去重，确保每个待更新的行只对应一个有效的源地址。以下是两种可行的修改方式：

方式1：用聚合函数取唯一地址

UPDATE
  my-project-19297-401423.housing_analysis.housing_data AS target
SET
  PropertyAddress = source.PropertyAddress
FROM (
  SELECT
    housing_1.UniqueID_,
    -- 提取同ParcelID下任意一个非空的PropertyAddress
    ANY_VALUE(housing_2.PropertyAddress) AS PropertyAddress
  FROM
    my-project-19297-401423.housing_analysis.housing_data AS housing_1
  JOIN
    my-project-19297-401423.housing_analysis.housing_data AS housing_2
  ON
    housing_1.ParcelID = housing_2.ParcelID
    AND housing_1.UniqueID_ <> housing_2.UniqueID_
    AND housing_2.PropertyAddress IS NOT NULL -- 仅匹配有有效地址的行
  WHERE
    housing_1.PropertyAddress IS NULL
  GROUP BY
    housing_1.UniqueID_ -- 确保每个UniqueID_只返回一行结果
) AS source
WHERE
  target.UniqueID_ = source.UniqueID_;

方式2：用窗口函数筛选唯一匹配行

UPDATE
  my-project-19297-401423.housing_analysis.housing_data AS target
SET
  PropertyAddress = source.PropertyAddress
FROM (
  SELECT
    housing_1.UniqueID_,
    housing_2.PropertyAddress,
    -- 给同UniqueID_的匹配行编号，仅保留第一行
    ROW_NUMBER() OVER(PARTITION BY housing_1.UniqueID_ ORDER BY housing_2.UniqueID_) AS rn
  FROM
    my-project-19297-401423.housing_analysis.housing_data AS housing_1
  JOIN
    my-project-19297-401423.housing_analysis.housing_data AS housing_2
  ON
    housing_1.ParcelID = housing_2.ParcelID
    AND housing_1.UniqueID_ <> housing_2.UniqueID_
    AND housing_2.PropertyAddress IS NOT NULL
  WHERE
    housing_1.PropertyAddress IS NULL
) AS source
WHERE
  target.UniqueID_ = source.UniqueID_
  AND source.rn = 1; -- 只取每个UniqueID_的第一个匹配结果

关键修改说明 #

• 新增子查询层，对匹配结果做去重处理，保证每个待更新的UniqueID_仅对应一个源地址
• 过滤housing_2中PropertyAddress为空的行，避免无效数据参与更新
• 用GROUP BY或ROW_NUMBER()将一对多的匹配关系转化为一对一，满足BigQuery的更新规则

内容的提问来源于stack exchange，提问作者Carlos E. Reyes Pineda