I’m trying to clean a BigQuery weather dataset where missing values were entered as 0. My UPDATE query to replace 0 with NULL is throwing an error. How can I correctly convert these zeroes to null values?
New contributor
Saiful Islam is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Add a comment
|
1 Answer
-- Option 1: Direct UPDATE (requires WHERE clause in BigQuery)
UPDATE `project.dataset.table_name`
SET temperature = NULL
WHERE temperature = 0;
-- Option 2: Using NULLIF (cleaner for multiple columns)
UPDATE `project.dataset.table_name`
SET
temperature = NULLIF(temperature, 0),
precipitation = NULLIF(precipitation, 0)
WHERE temperature = 0 OR precipitation = 0;
-- Option 3: CREATE new table (safer, no data loss risk)
CREATE OR REPLACE TABLE `project.dataset.cleaned_table` AS
SELECT
* EXCEPT(temperature, precipitation),
NULLIF(temperature, 0) AS temperature,
NULLIF(precipitation, 0) AS precipitation
FROM `project.dataset.original_table`;
New contributor
Gregory B is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.