BigQuery: Updating RECORD REPEATED field on Bigquery

Question

I am trying to update a table (business-intelligence-251208.WIS_DWH.testUpdate) which has a RECORD REPEATED called eventInfo, based on another table, I am executing below query

UPDATE `business-intelligence-251208.WIS_DWH.testUpdate`
SET eventInfo = ARRAY(
  SELECT AS STRUCT * REPLACE(e.eventCategory AS eventCategory,
                             e.eventAction AS eventAction,
                              n.newOperator AS operator,
                              e.numberEvents AS numberEvents,
                              e.numberUsers AS numberUsers)
  FROM UNNEST(eventInfo) AS e,
       (select operator as newOperator , pretty_link from  `business-intelligence-251208.WIS_DWH.OPERATOR_MAPPING_TPP`) AS n
  WHERE (e.eventAction = LOWER(n.pretty_link))
  AND e.operator = 'Operator Not Mapped'
)
WHERE TRUE;

but I am getting an error: Value of type ARRAY<STRUCT<eventCategory STRING, eventAction STRING, operator STRING, ...>> cannot be assigned to eventInfo, which has type ARRAY<STRUCT<eventCategory STRING, eventAction STRING, operator STRING, ...>> at [2:17]

This is the structure of the table I am trying to update: enter image description here

Any help ond this is really appreciated!

Answer 1

Make sure that the data types of the source and destination tables are the same, as the truncated error message suggests, the first 3 columns, eventCategory, eventAction and operator, are all STRING type and you are left with the numberEvents and numberUsers which should be an INTEGER.

The SELECT * REPLACE clause can actually change the value type of your data.

A SELECT * REPLACE statement doesn't change the names or order of columns. However, it can change the value and the value type.