One thing I’ve learned with BigQuery is that when a simple CASE statement fails, the root cause is often type consistency rather than the logic itself.

A CASE expression must resolve to a single data type across all branches. If you’re replacing 0 with NULL, BigQuery needs to be able to infer that the NULL is compatible with the target column’s type.

For example, if the column is numeric, BigQuery can usually infer the type correctly. But if there are casts, mixed types, or schema constraints involved, the update may fail even though the logic looks valid.

I’d check a few things:

• Does the target column allow NULL values?

• Are all branches of the CASE returning the same data type?

• Does a simple SELECT with the same CASE expression work before running the UPDATE?

• Is the table partitioned, clustered, or subject to constraints that could affect updates?

My general debugging strategy is to test the expression independently first:

SELECT
  CASE
    WHEN col = 0 THEN NULL
    ELSE col
  END AS updated_value
FROM table_name;

If that works, the issue is usually with the update operation or schema rather than the CASE itself.

What error message surprised me the first time I ran into this was how often BigQuery points to a type mismatch even when the intent seems obvious to us. SQL engines care a lot more about type resolution than human readers do.

One thing I’d check first is whether the issue is actually the CASE statement or the data type of the column being updated.

In BigQuery, NULL is typeless until it’s cast or inferred. If you’re replacing a numeric 0 with NULL, the expression returned by the CASE must still resolve to a consistent type. For example:

CASE
  WHEN column_name = 0 THEN NULL
  ELSE column_name
END

usually works because BigQuery can infer the type from column_name. But if the column is being transformed alongside other expressions, or if multiple branches return different types, BigQuery may reject the update.

Another thing to verify is whether you’re updating a partitioned or clustered table with constraints, or attempting to write a value that doesn’t match the target schema.

My debugging approach would be:

1. Run the CASE expression in a SELECT first.

2. Confirm the output type using TYPEOF().

3. Check whether all branches of the CASE return the same data type.

4. Verify that the target column allows NULL values.

In my experience, most “replace 0 with NULL” update failures end up being type-resolution issues rather than a problem with the CASE logic itself.

What exact error message is BigQuery returning? That usually points directly to whether it’s a typing, schema, or update constraint issue.