CODEWITHSUNDEEP
google-bigquery
KayEss
KayEss

Reputation: 419

Replace NULLs with fixed values without creating new column BigQuery

I know it has been written so much about this but i still didn't find an appropriate answer to my question. I have a couple of columns that involve NULLS. I want to replace all NULLs with 0. With every method i used i needed to create new columns with new(old) values instead of just replace values in an existing columns. Am i doing something wrong or that's just the way it is?

The data looks like this

 SerialNumber Distance    Heading   Speed_diff   Speed  
 123             NULL       NULL     NULL         45         
 123              1         -1        5           50        
 123              2          2        10          60

I used following query:

WITH remove_nulls as (SELECT *,
         IFNULL(Distance_meters,0) as Distance_meters,
         IFNULL(Heading_radians,0) AS Heading_radians,
         IFNULL(Speed_difference,0) AS Speed_difference, 
         from xxx.yyy.zzz)

Output:

 SerialNumber Distance    Heading   Speed_diff   Speed  Distance_1 Heading_1 Speed_diff_1
 123             NULL       NULL     NULL         45         0        0          0
 123              1         -1        5           50        -1       -1          5
 123              2          2        10          60         2        2          10

The problem is, that instead of replacing NULLs in existing columns i always form new columns! Is there a way to avoid it and basically only replace values in existing columns?

Desired output:

 SerialNumber Distance    Heading   Speed_diff   Speed  
 123              0          0        0           45         
 123              1         -1        5           50        
 123              2          2        10          60

Upvotes: 1

Views: 635

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

You were almost there - use below

with remove_nulls as (
  select * replace(
    ifnull(Distance,0) as Distance,
    ifnull(Heading,0) AS Heading,
    ifnull(Speed_diff,0) AS Speed_diff) 
  from `xxx.yyy.zzz`
)

with output

enter image description here

Upvotes: 1

Pentium10
Pentium10

Reputation: 207838

Yes there is with the EXCEPT syntax

WITH remove_nulls as (SELECT * EXCEPT(Distance_meters,Heading_radians,Speed_difference),
         IFNULL(Distance_meters,0) as Distance_meters,
         IFNULL(Heading_radians,0) AS Heading_radians,
         IFNULL(Speed_difference,0) AS Speed_difference, 
         from xxx.yyy.zzz)

Upvotes: 1

Related Questions