IT Q&A

Welcome!

This community is for professionals and enthusiasts of our products and services.
Share and discuss the best content and new marketing ideas, build your professional profile and become a better marketer together.

0

Invalidate all emails in a database

Avatar
Corpuz Angel

If you ever need to make sure that email addresses for all users are invalid, you can run the following queries on the database. The emails will have their original domain substituted with the non routable domain @example.com.

Different queries are needed for the res_user and the res_partner table.

Caveat Utilitor
THIS CHANGE IS IRREVERSIBLE!!! BE CAREFUL AND YOU HAVE BEEN WARNED.


Select all emails from res_partner and show the changes that will be applied

SELECT  email,
        substr(email, strpos(email, '@') + 1) as DOMAIN,
        REPLACE (email, substr(email, strpos(email, '@') + 1), 'example.com') AS result
FROM res_partner WHERE email IS NOT null and bool(strpos(email, '@'));

Apply the changes to the res_partner table

UPDATE res_partner set
email = REPLACE (email, substr(email, strpos(email, '@') + 1), 'example.com')
WHERE email IS NOT null and bool(strpos(email, '@'));

____

Select all email logins from res_users and show the changes that will be applied

SELECT  login,
        substr(login, strpos(login, '@') + 1) as DOMAIN,
        REPLACE (login, substr(login, strpos(login, '@') + 1), 'example.com') AS result
FROM res_users WHERE login IS NOT null and bool(strpos(login, '@'));

Apply the changes to the res_users table

UPDATE res_users SET
login = REPLACE (login, substr(login, strpos(login, '@') + 1), 'example.com')
WHERE login IS NOT null and bool(strpos(login, '@'));



Avatar
Abbandona