本文介绍了在左联接和交叉联接BigQuery中使用更新的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我已经发布的问题中有一个后续问题.
I have a followup question on my already posted question.
我试图获得相同的结果,但还添加了交叉联接.
I amt trying to achieve the same result but also adding Cross Join in it.
update Table1
set ColumnTest = ifnull(b.value, 'no run')
From left join
(select distinct h.eventinfo.eventcategory as ID,value
FROM Table2
cross join (hits)h )
where Table1.ID= Table2.ID
我有2个表Table1和Table2
I have 2 tables Table1 and Table2
我想用Table2.Value更新Table1.ColumnTest
I want to update Table1.ColumnTest with Table2.Value
其中Table1.ID = Table2.hits.eventInfo.eventCategory(嵌套表格2)
where Table1.ID= Table2.hits.eventInfo.eventCategory (Unnest Table2)
,如果Table1.ID<>然后将Table2.hits.eventInfo.eventCategory更新为不运行"
and if Table1.ID <> Table2.hits.eventInfo.eventCategory then update Table1.ColumnTest with 'no run'
感谢您的帮助!
推荐答案
尝试以下方法
UPDATE `table1` t1
SET ColumnTest = IFNULL(t2.value, 'no run')
FROM (
SELECT id, value
FROM `table1`
LEFT JOIN (
SELECT hit.eventInfo.eventCategory AS id, value
FROM `table2`
CROSS JOIN UNNEST (hits) AS hit
)
USING(id)
) t2
WHERE t1.ID = t2.ID
这篇关于在左联接和交叉联接BigQuery中使用更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!