Replacing Zeros with Null Values - Error Encountered

Question

I'm working with weather data in BigQuery and need to replace zeroes (mistakenly entered for missing values) with nulls before analyzing wind speed and visibility. My current code results in an error.

Can anyone help me with the correct syntax to achieve this null value replacement? Sharing the relevant code snippet and error message would be greatly appreciated.

Answer 1

The following should work:

UPDATE tablename
SET columnname = NULL
WHERE columnname = 0

The only potential error I can think of is if the column is declared NOT NULL. You will need to use ALTER TABLE first to enable null values in the column.

Answer 2

In BigQuery, you can use the NULLIF() function to replace zeroes with null values. Here's how you can update the existing table directly:

UPDATE your_table
SET wind_speed = NULLIF(wind_speed, 0), #adjust the column name wind_speed
    visibility = NULLIF(visibility, 0)  #adjust the column name visibility
WHERE wind_speed = 0 OR visibility = 0;

Instead of updating the existing table, you can use similar approach to create view of the table:

SELECT
  desired_columns, #replace with all the desired column names.
  NULLIF(wind_speed, 0) AS wind_speed,
  NULLIF(visibility, 0) AS visibility,
  -- include other columns here
FROM
  your_table_name