BigQuery updating regular field with record field

Question

I have a table with few regular fields and one repeated record field: batch_number<INT64>, data array(start_date<DATE>, end_date<DATE>, ...). I am trying to update batch_number field based on condition in the nested field. But my update changes all rows in the table:

UPDATE ds.table as t
SET batch_number = 2
FROM ds.table i, UNNEST(data) as u
WHERE u.start_date = '2021-03-04'

Why is this and what is proper syntax in my case? Thank you!

Answer 1

You can try following SQL:-

UPDATE ds.table 
SET batch_number = 2 
WHERE '2021-03-04' IN (SELECT start_date FROM UNNEST(data))