How can add a column that shows the day of the week in BigQuery?

Question

Im looking to show the day of the week that pertains to the date in my table. The table is under bellabeat-case-study-2-376122.fitbit_data.daily_activity and the column I'm pulling from is ActivityDate. I'm looking to create a column titled DayofWeek.

I've attempted to create a column and insert the data into the column, but it keeps coming back null for the results.

##Created a column
ALTER TABLE `bellabeat-case-study-2-376122.fitbit_data.daily_activity`
ADD COLUMN DayofWeek INT64;```

##Attempted to insert the data
INSERT INTO `bellabeat-case-study-2-376122.fitbit_data.daily_activity` (DayofWeek)
SELECT EXTRACT(DAYOFWEEK FROM ActivityDate)
FROM `bellabeat-case-study-2-376122.fitbit_data.daily_activity`

Answer 1

As @JNevill mentioned in the comments:

You don't want to INSERT, you want to UPDATE:

UPDATE bellabeat-case-study-2-376122.fitbit_data.daily_activity SET DayofWeek =  EXTRACT(DAYOFWEEK FROM ActivityDate)

That being said, your INSERT should have still worked, but it would have created a set of new records equal in row count to your existing table (essentially doubling the rows each time you run it).

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

Feel free to edit this answer for additional information.