TigSh
TigSh

Reputation: 645

Using update with Left Join and cross join BigQuery

I have a followup question on my already posted question.

Using update with 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

I have 2 tables Table1 and Table2

I want to update Table1.ColumnTest with Table2.Value

where Table1.ID= Table2.hits.eventInfo.eventCategory (Unnest Table2)

and if Table1.ID <> Table2.hits.eventInfo.eventCategory then update Table1.ColumnTest with 'no run'

Thanks for you help!!

Upvotes: 0

Views: 476

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

Try below

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 

Upvotes: 2

Related Questions