I have a table with two columns: group_id, students (Array of student). Each student have a two attribute: name and last_name. How do you swap name with last_name for each student?
x
Students table:
group_id | students.name | students.last_name
---------------------------------------------
1 | Perez | Juan
---------------------------------------------
2 | Caicedo | Pedro
I need to swapping students.name to students.last_name, any idea?
Advertisement
Answer
Finally, thanks to Mikhail Berlyant who answered this question (Update values in struct arrays in BigQuery), i was able to exchange attributes on update clause.
UPDATE `Students` s
SET students =
ARRAY(
SELECT
AS STRUCT
last_name, name
FROM s.students
)
WHERE TRUE;
Too, i was able to exchange attributes on update clause with if condition for example.
UPDATE `Students` s
SET students =
ARRAY(
SELECT
AS STRUCT
IF(CONTAINS_SUBSTR(name, 'Pedro'), name, last_name), IF(CONTAINS_SUBSTR(last_name, 'Caicedo'), last_name, name)
FROM s.students
)
WHERE TRUE;
It’s very important the position in the STRUCT, in my case name is first and last_name is second, for achieve the exchange just by changing its position the objective is achieved.