BigQuery：基于关联表条件更新已有列

问题说明 #

现有两张表t1和t2，需通过obid字段关联。要求当t2.inact为空且t2.objval=1000时，将t1的blocked列设置为'X'。

表结构与预期结果 #

• 初始表t1：

• 表t2：

• 更新后预期t1：

过往尝试的问题 #

尝试用WITH CTE结合UPDATE的写法在BigQuery中不被支持；改用CASE语句仅能生成新列，无法更新原表的blocked字段。

尝试的SQL代码：

WITH abc AS(
SELECT obid,blocked
FROM table1),
def AS (
SELECT obid,inact,objval,
FROM table2
WHERE objval = '1000')

SELECT CASE WHEN t2.inact = '' THEN 'X'
        ELSE '' END as blocked
        FROM abc t1
JOIN def t2
ON t2.obid = t1.obid

正确实现方案 #

BigQuery支持UPDATE ... FROM语法实现跨表关联更新，直接对原表字段进行修改：

UPDATE `你的项目ID.你的数据集ID.t1` t1
SET blocked = 'X'
FROM `你的项目ID.你的数据集ID.t2` t2
WHERE t1.obid = t2.obid
  AND t2.objval = 1000
  AND (t2.inact IS NULL OR t2.inact = '')

关键说明 #

1. 语法适配：UPDATE ... FROM是BigQuery官方支持的跨表更新方式，可直接关联目标表与参照表。
2. 空值兼容：同时处理t2.inact为NULL或空字符串的情况，覆盖表结构中显示的空值场景。
3. 精准更新：仅满足t2.objval=1000且obid匹配的t1行才会被修改，其余行保持原blocked值不变。

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