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

Question

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.

please provide to us the exception you are receiving

Panciz
– Panciz

2019-06-11 14:30:40 +00:00
Commented Jun 11, 2019 at 14:30 — Panciz
– Panciz, Commented Jun 11, 2019 at 14:30

Mikhail Berlyant · Accepted Answer · 2019-06-11 22:17:50Z

1

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)

answered Jun 11, 2019 at 22:17

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related