CODEWITHSUNDEEP
sqlgoogle-bigquery
Linda
Linda

Reputation: 11

BigQuery save query as column in existing table

I have a table (all_tripdata) that contains data about trips made with a bike sharing platform. The table has columns ride_id, started_at (timestamp) and ended_at (timestamp). I want to calculate the length of these trips using this query:

SELECT
  ride_id,
  started_at,
  ended_at,
  member_casual,
  TIMESTAMP_DIFF(ended_at, started_at, SECOND) AS ride_len_sec,
FROM tripdata.all_tripdata;

This gives a new column: ride_len_sec with the duration of each trip in seconds. How can I save this column into the existing all_tripdata table?

I tried ride_len_sec into a new table using SELECT INTO but this doesn't seem to work in BigQuery

Upvotes: 0

Views: 64

Answers (2)

Francisco LC
Francisco LC

Reputation: 1

The workaround I found was to do it in two steps:

1.- Add an empty column to your table using this syntax:

ALTER TABLE tripdata.all_tripdata ADD COLUMN ride_len_sec INT64

This creates the new column in the same table.

2.- Update that new column with the calculation:

UPDATE tripdata.all_tripdata SET ride_len_sec = (TIMESTAMP_DIFF(ended_at, started_at, SECOND)) WHERE TRUE;

This calculates the length in the new column in the same table.

Upvotes: 0

Scott B
Scott B

Reputation: 3004

As mentioned by @Jaytiger in the comments:

use CREATE OR REPLACE TABLE tripdata.all_tripdata AS SELECT ..... -- your query;

Posting the answer as community wiki as this is the BEST PRACTICE and for the benefit of the community that might encounter this use case in the future.

Upvotes: 0

Related Questions