UPDATE dataset_a.table_aAS n SET nested_record_datatype_column_with_repeated_mode = ARRAY( SELECT AS STRUCT col2, '', '', '', col5, '' FROMdataset_b.table_Btm WHERE tm.common_column_to_join = n.common_column_to_join ) WHERE EXISTS ( SELECT 1 FROMdataset_c.table_c AS dm WHERE n.common_column_to_join = dm.common_column_to_join )
dataset_a.table_a have column with record dataype with repeated mode - nested_record_datatype_column_with_repeated_mode.
I am trying to update the values of bigquery table which has record dataype with repeated mode. I want to select the Column values from another table and update those values into that nested column. I have searched everywhere but could not find any solution which is working.
I tried using select as sturct. But that gave an error - Value of type ARRAY<STRUCT<business_name STRING, addr_line_1 STRING, STRING, ...>> cannot be assigned to golden_account_records, which has type ARRAY<STRUCT<business_name STRING, addr_line_1 STRING, addr_line_2 STRING, ...>>
I have verified all the columns and their datatypes if they are matching with source and destination tables and everything is correct.