英文:
Updating a struct column in bigquery
问题
我有一个表格，其中包含1个普通模式列 id 和一个结构化（record, repeated）列 details。
details 列内嵌了 country 和 name 列。
在 details 列内有五个记录。
country name
UK sarah
IND carter
Itay marie
France john
peru christy
我正在使用以下查询来更新单个记录的值。
update
info_table
set value=array(
select as struct * replace('US' as country)) from unnest(details)
where name='sarah'
)
where id=100
这将成功将国家值从英国（UK）更新为美国（US），但这将删除其他4条记录。
我需要一个查询来更新结构字段而不混淆其他记录。
英文:
I have a table with 1 normal mode column id and a struct(record, repeated) column details.
The details column has country and name column nested inside that.
I have five records inside the details column.
country name
UK sarah
IND carter
Itay marie
France john
peru christy
I am using the below query to update the value of a single record.
update
info_table
set value=array(
select as struct * replace('US' as country)) from unnest(details)
where name='sarah'
)
where id=100
This updates the country value from UK to US successfully, however, this does remove the rest of the 4 records.
I need a query to update the struct fields without messing up the other records.
答案1
得分: 1
UPDATE info_table
SET details = (
SELECT ARRAY(
SELECT AS STRUCT
IF(name = 'sarah', 'US', country) AS country,
name
FROM UNNEST(details)
)
)
WHERE id = 100
英文:
Try the following :
UPDATE info_table
SET details = (
SELECT ARRAY(
SELECT AS STRUCT
IF(name = 'sarah', 'US', country) AS country,
name
FROM UNNEST(details)
)
)
WHERE id = 100
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论