BigQuery中如何用同ParcelID行值更新空PropertyAddress并解决匹配错误

错误原因 #

你遇到的UPDATE/MERGE must match at most one source row for each target row错误，是因为待更新的行（t1）通过JOIN匹配到了多个同ParcelID的t2行，数据库无法确定使用哪一个t2的地址值进行填充。

解决方案 #

方法1：使用聚合函数获取唯一有效地址 #

利用同一个ParcelID下的非空地址应该一致的特性，用聚合函数（MAX/MIN）确保每个目标行只匹配一个源地址：

UPDATE portfolioproj2.nashvillehousing.realhousingdata AS t1
SET PropertyAddress = (
    SELECT MAX(t2.PropertyAddress)
    FROM portfolioproj2.nashvillehousing.realhousingdata AS t2
    WHERE t2.ParcelID = t1.ParcelID
      AND t2.PropertyAddress IS NOT NULL
)
WHERE t1.PropertyAddress IS NULL;

方法2：使用窗口函数预筛选有效地址 #

先通过窗口函数为每个ParcelID提取第一个非空地址，再关联更新：

WITH AddressLookup AS (
    SELECT 
        ParcelID,
        FIRST_VALUE(PropertyAddress) OVER (
            PARTITION BY ParcelID 
            ORDER BY CASE WHEN PropertyAddress IS NOT NULL THEN 0 ELSE 1 END
        ) AS ValidAddress
    FROM portfolioproj2.nashvillehousing.realhousingdata
)
UPDATE portfolioproj2.nashvillehousing.realhousingdata AS t1
SET PropertyAddress = al.ValidAddress
FROM AddressLookup AS al
WHERE t1.ParcelID = al.ParcelID
  AND t1.PropertyAddress IS NULL;

原代码问题说明 #

原代码中，t1.UniqueID_ <> t2.UniqueID_的条件会让每个空地址行匹配所有同ParcelID的其他行，若同ParcelID存在多个非空地址行，就会形成多对一的匹配关系，触发数据库的行匹配限制错误。

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