Search code examples
sqlgoogle-bigquerymaxwindow-functionsmissing-data

Replace missing value with similar row values in same dataset (no joins)

After completing necessary table joins I want to replace missing values in a column where the correct value exists in that same column already. My desired approach is "if you see this row's same email elsewhere, grab the corresponding name value and replace the empty string with that."

Example:

email.       name 
[email protected]  john 
[email protected]  ''

Desired output:

email.       name 
[email protected]  john 
[email protected]  john

I want to fill the '' with john because the query knows their emails are exactly alike.

Assuming this could benefit from an IFNULL but any help would be welcomed.

Solution

  • You can use MAX() window function:

    SELECT email,
       MAX(name) OVER (PARTITION BY email) name
FROM tablename;