BigQuery UPDATE multiple nested columns

Question

I would like to understand the principle of how to update a BigQuery Nested table.

For example, with have a table:

ID   GROSS   OL.ID  OL.GROSS   PL.ID   PL.GROSS
1    100     12     200        34      454
             34     465        56      5667
             67     7876

And I would like to update the gross value for the main, OL. and PL. to any other value

What is the way to do it?

Mikhail Berlyant · Accepted Answer · 2019-07-17 20:36:33Z

2

Below is for BigQuery Standard SQL and extends Elliott's answer

Usually, there are more than just two elements in struct and you need to update just one or two - instead of explicitly calling them all out to rebuild original array - you can use below approach

UPDATE `project.dataset.table` SET
  OL = ARRAY(SELECT AS STRUCT * REPLACE(1.1 * GROSS AS GROSS) FROM UNNEST(OL)),
  PL = ARRAY(SELECT AS STRUCT * REPLACE(1.2 * GROSS AS GROSS) FROM UNNEST(PL))

answered Jul 17, 2019 at 20:36

Mikhail Berlyant

174k10 gold badges172 silver badges250 bronze badges

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

Comments

Elliott Brossard · Accepted Answer · 2019-07-17 13:04:36Z

1

I'm assuming that OL and PL are arrays in this example but it's hard to tell. You can use a query like this:

UPDATE dataset.table
SET
  OL = ARRAY(SELECT AS STRUCT ID + 2 AS ID, GROSS + 3 AS GROSS FROM UNNEST(OL)),
  PL = ARRAY(SELECT AS STRUCT 1 AS ID, GROSS + 3 AS GROSS FROM UNNEST(PL))

See also the UPDATE repeated records example in the documentation

answered Jul 17, 2019 at 13:04

Elliott Brossard

34k2 gold badges75 silver badges105 bronze badges

Collectives™ on Stack Overflow

BigQuery UPDATE multiple nested columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related