CODEWITHSUNDEEP
google-bigquery
sheik abdullah
sheik abdullah

Reputation: 1

How to update a nested bigquery column with data from another bigquery table

I have 2 bigquery tables with nested columns, I need to update all the columns in table1 whenever table1.value1=table2.value, also those tables having a huge amount of data.

I could update a single nested column with static column like below,

    #standardSQL
    UPDATE `ck.table1` 
    SET promotion_id = ARRAY(
      SELECT AS STRUCT * REPLACE (100 AS PromotionId ) FROM UNNEST(promotion_id)
    )

But when I try to reuse the same to update multiple columns based on table2 data I am getting exceptions.

I am trying to update table1 with table2 data whenever the table1.value1=table2.value with all the nested columns.

As of now, both tables are having a similar schema.

Upvotes: 0

Views: 524

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172984

I need to update all the columns in table1 whenever table1.value1=table2.value
... both tables are having a similar schema

I assume by similar you meant same

Below is for BigQuery Standard SQL
You can use below query to get combining result and save it back to table1 either using destination table or CREATE OR REPLACE TABLE syntax 

#standardSQL
SELECT AS VALUE IF(value IS NULL, t1, t2)
FROM `project.dataset.table1` t1
LEFT JOIN `project.dataset.table2` t2
ON value1 = value

I have not tried this approach with UPDATE syntax - but you can try and let us know :o)

Upvotes: 1

Related Questions