将更新与Left Join BigQuery结合使用 [英] Using update with Left Join BigQuery
问题描述
我正在尝试使用BigQuery中的LEFT JOIN编写更新查询,但不确定如何编写.
I am trying to write an Update query with LEFT JOIN in BigQuery but I am not sure how to write it.
update Table1
set ColumnTest = ifnull(b.value, 'no run')
From left join (select distinct ID,value FROM Table2 where value = 10) B --
where Table1.ID= Table2.ID
我有2个表Table1和Table2
I have 2 tables Table1 and Table2
我想用Table2.Value更新Table1.ColumnTest,其中Table1.ID = Table2.ID并且如果Table1<>然后,Table2使用"no run"更新Table1.ColumnTest
I want to update Table1.ColumnTest with Table2.Value where Table1.ID= Table2.ID and if Table1 <> Table2 then update Table1.ColumnTest with 'no run'
谢谢!
新尝试
UPDATE Table1
SET LP = IFNULL(t2.value, 'no run')
FROM ( select distinct hits.eventInfo.eventCategory as ID, value
FROM Table2
CROSS JOIN UNNEST (hits) AS hits
left join TAble1 using (hits.eventInfo.eventCategory)
WHERE) t2
WHERE t1.ID = t2.ID
错误:语法错误:预期为)";或,"但得到了."
Error: Syntax error: Expected ")" or "," but got "."
推荐答案
我想用Table2.Value更新Table1.ColumnTest,其中Table1.ID = Table2.ID,如果Table1是<然后,Table2使用"no run"更新Table1.ColumnTest
I want to update Table1.ColumnTest with Table2.Value where Table1.ID= Table2.ID and if Table1 <> Table2 then update Table1.ColumnTest with 'no run'
以下是用于BigQuery标准SQL
Below is for BigQuery Standard SQL
UPDATE `table1` t1
SET ColumnTest = IFNULL(value, 'no run')
FROM (
SELECT id, value
FROM `table1`
LEFT JOIN `table2`
USING(id)
) t2
WHERE t1.id = t2.id
这篇关于将更新与Left Join BigQuery结合使用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!