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 
abc@aol.com  john 
abc@aol.com  ''

Desired output:

email.       name 
abc@aol.com  john 
abc@aol.com  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.

Answer

You can use MAX() window function:

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

Advertisement

Answer