Updating a column from a calculated field using UPDATE, SUM & EXTRACT functions

Russel Orodio 0
Viewed 52

I want to update the 'duration_s' column (numeric), so I can obtain the duration in minutes & hours and also obtain velocity, using the given distance traveled.

but first I need to transform ride_length (schema - time) into an integer

so I tried, but I am stuck. this is the query I tried to write:

WITH calc AS (
      SELECT 
      SUM((EXTRACT(HOUR FROM ride_length)*3600)+(EXTRACT(MINUTE FROM ride_length)*60)+EXTRACT(SECOND FROM ride_length)) AS seconds
      FROM `fresh-ocean-357202.Cyclistic.Cyclistic_yearly`
)
UPDATE `fresh-ocean-357202.Cyclistic.Cyclistic_yearly`
SET duration_s = calc.seconds
WHERE TRUE

these field names have NULL are set as NUMERIC as its data type

these are the columns that Im working on

sql-update google-bigquery sql
1 Answers

I already got it. Instead of doing this:

UPDATE `fresh-ocean-357202.Cyclistic.Cyclistic_yearly` 
SET duration_s = SUM((EXTRACT(HOUR FROM ride_length)*3600)+(EXTRACT(MINUTE FROM ride_length)*60)+EXTRACT(SECOND FROM ride_length))
WHERE TRUE

I did this:

UPDATE `fresh-ocean-357202.Cyclistic.Cyclistic_yearly` 
SET duration_s = (EXTRACT(HOUR FROM ride_length)*3600)+(EXTRACT(MINUTE FROM ride_length)*60)+EXTRACT(SECOND FROM ride_length)
WHERE TRUE

I just removed the SUM function into the SET function

Russel Orodio0
Related