在BigQuery中加入CTE结果时如何更新表? [英] How to update table while joining on CTE results in BigQuery?
问题描述
我认为通过这个简化的示例(在PostgreSQL中可以工作)可以看到我正在尝试做什么....
I think it will be obvious to see what I'm trying to do through this simplified example (works in PostgreSQL)....
with a as
(
select 1 as id, 123.456 as value
)
update
mytable
set value = coalesce(a1.value, a2.value)
from
a as a1,
a as a2
where
a1.id = mytable.id
or
a2.id = mytable.id2
这是一个简化的示例.实际上,"a"表达式非常复杂,我需要在update表达式中多次加入该表达式.有没有办法在BigQuery的一条语句中做到这一点?
This is a simplified example. In reality the "a" expression is pretty complex and I need to join to it multiple times in the update expression. Is there a way to do this in one statement in BigQuery?
现在,我看到的唯一选择是首先在单独的查询中将"a"创建为表,然后执行更新.可以,但是这需要经常按计划运行,因此不是最佳选择.
Right now, the only option I see is to first create "a" as a table in a separate query, and then perform the update. That can work, but this is something that needs to run frequently on a schedule so it's not optimal.
推荐答案
您可以将update语句转换为下面的
You can transform you update statement into below
#standardSQL
UPDATE
`project.dataset.mytable` mytable
SET value = new_value
FROM (
WITH a AS
(
SELECT 1 AS id, 123.456 AS value
)
SELECT a1.id a1_id, a2.id a2_id,
COALESCE(a1.value, a2.value) new_value
FROM
a AS a1,
a AS a2
)
WHERE
a1_id = mytable.id
OR
a2_id = mytable.id2
这篇关于在BigQuery中加入CTE结果时如何更新表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!