Reputation: 645
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
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