在左联接和交叉联接BigQuery中使用更新 [英] Using update with Left Join and cross join BigQuery

问题描述

我已经发布的问题中有一个后续问题.

I have a followup question on my already posted question.

使用带有Left Join BigQuery的更新

我试图获得相同的结果，但还添加了交叉联接.

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中使用更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！