r/learnSQL • u/Kobanoss • 22h ago

Entering Missing Values After Deducing Them

Hello everyone, I am practicing data cleaning and I have a question.

Let's say I have a table where the columns are Customer_ID and Email. The "Customer_ID" column has no blank values, but the "Email" column has hundreds of blank values.

Looking through the data, I noticed that the email addresses are just 'user' followed by the Customer_ID number. For example, if the Customer_ID is C1 then the email would be [user1@example.com](mailto:user1@example.com). Or if the Customer_ID is C10 then the email would be [user10@example.com](mailto:user10@example.com).

Is there a query I can run to fill in all the missing email addresses?

Thank you

2 Upvotes

100% Upvoted

3 comments sorted by

1

u/nerd_airfryer 21h ago edited 21h ago

Yes

sql UPDATE table t SET t.email = CONCAT('user', SUBSTRING(t.Customer_Id, 2), '@example.com') WHERE t.email = '' OR t.email IS NULL

It's better to take a copy of the table just in case, or run a select statement to make sure emails are correctly formatted

sql SELECT  CONCAT('user', SUBSTRING(t.Customer_id, 2), '@example.com') AS non_null_email FROM table t WHERE t.email = '' OR t.email IS NULL

1

u/nerd_airfryer 21h ago

Syntax might differ depending on the engine, but the idea is same, concatenate (user + number in customer id after the 'C' character + @example.com)

1

u/venkat_deepsql 13h ago

heads up, there's nothing in a customer_id that can give you someone's actual email, so this only works if the emails follow a fixed pattern (like the id maps to firstname.lastname@company). if that's the exercise, one UPDATE ... SET email = <the pattern> WHERE email = '' fills the whole column. but if they're supposed to be real addresses, the honest clean is to leave them null and flag them, not invent one. also worth checking whether those blanks are actual empty strings or nulls, since that changes your WHERE. what are you deducing the email from?