BigQuery使用COALESCE更新表时匹配行数错误的解决求助

问题背景 #

跟着YouTube上的SQL Server数据清洗教程学习，因Mac无法安装SQL Server，转用BigQuery适配语法。填充PropertyAddress字段的查询语句已适配成功，但执行UPDATE时接连报错：

1. 首次执行UPDATE时提示表“a”必须使用数据集限定
2. 修正表名后又提示UPDATE/MERGE必须为每个目标行匹配至多一个源行

原SQL Server语句 #

Select *
From PortfolioProject.dbo.NashvilleHousing
--Where PropertyAddress is null
order by ParcelID

Select a.ParcelID, a.PropertyAddress, b.ParcelID, b.PropertyAddress, ISNULL(a.PropertyAddress,b.PropertyAddress)
From PortfolioProject.dbo.NashvilleHousing a
JOIN PortfolioProject.dbo.NashvilleHousing b
    on a.ParcelID = b.ParcelID
    AND a.[UniqueID ] <> b.[UniqueID ]
Where a.PropertyAddress is null

Update a
SET PropertyAddress = ISNULL(a.PropertyAddress,b.PropertyAddress)
From PortfolioProject.dbo.NashvilleHousing a
JOIN PortfolioProject.dbo.NashvilleHousing b
    on a.ParcelID = b.ParcelID
    AND a.[UniqueID ] <> b.[UniqueID ]
Where a.PropertyAddress is null

已适配的BigQuery查询语句 #

Select *
From `sturdy-filament-415311.NashvilleHousing.NHData`
order by ParcelID
;

Select a.ParcelID, a.PropertyAddress, b.ParcelID, b.PropertyAddress, COALESCE(a.PropertyAddress,b.PropertyAddress)
From `sturdy-filament-415311.NashvilleHousing.NHData` a
JOIN `sturdy-filament-415311.NashvilleHousing.NHData` b
  on a.ParcelID = b.ParcelID
  AND a.UniqueID_ <> b.UniqueID_
Where a.PropertyAddress is null
;

错误原因分析 #

1. 表“a”必须使用数据集限定：BigQuery的UPDATE语法不支持直接用别名作为目标表，必须指定完整的数据集限定表名（即项目ID.数据集.表名格式）。
2. UPDATE/MERGE必须为每个目标行匹配至多一个源行：原JOIN逻辑中，同一个ParcelID可能对应多个非空PropertyAddress的行，导致待更新行（a.PropertyAddress IS NULL）匹配到多个源行，BigQuery不允许这种多对一的UPDATE操作，必须保证每个待更新行仅对应一个唯一的更新值。

解决方案 #

方案1：使用聚合函数快速匹配（适合同一ParcelID地址一致场景） #

用MAX()获取每个ParcelID下的非空地址，确保每个待更新行仅匹配一个值：

UPDATE `sturdy-filament-415311.NashvilleHousing.NHData`
SET PropertyAddress = (
  SELECT MAX(b.PropertyAddress)
  FROM `sturdy-filament-415311.NashvilleHousing.NHData` b
  WHERE b.ParcelID = ParcelID
    AND b.PropertyAddress IS NOT NULL
)
WHERE PropertyAddress IS NULL;

方案2：窗口函数贴合原教程逻辑（排除自身UniqueID） #

用FIRST_VALUE优先取同ParcelID下的非空地址，若存在多个非空值则按UniqueID排序取第一个：

WITH address_filled AS (
  SELECT 
    UniqueID_,
    ParcelID,
    PropertyAddress,
    FIRST_VALUE(PropertyAddress) OVER (
      PARTITION BY ParcelID 
      ORDER BY CASE WHEN PropertyAddress IS NOT NULL THEN 0 ELSE 1 END, UniqueID_
    ) AS filled_address
  FROM `sturdy-filament-415311.NashvilleHousing.NHData`
)
UPDATE `sturdy-filament-415311.NashvilleHousing.NHData` t
SET PropertyAddress = af.filled_address
FROM address_filled af
WHERE t.UniqueID_ = af.UniqueID_
  AND t.PropertyAddress IS NULL;

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