BigQuery : How to extract year from existing datetime column and insert into the table as new specific year column?

Question

I have an actual bigquery table with a datetime column, and i want to split this datetime in other columns such as : year, month, day, quarter

I have succeeded the extract with the query :

SELECT date, EXTRACT(YEAR FROM date) as year, EXTRACT(MONTH FROM date) as month, EXTRACT(QUARTER FROM date) as quarter FROM 'project.dataset.table' ;

How to get the result and update the table by creating the new year/month/quarter columns ?

I tried the method : SET year = EXTRACT(YEAR FROM date) WHERE TRUE

but it didn't work

Answer 1

you need to use the query in the scope of a DML UPDATE statement https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#update_statement