CODEWITHSUNDEEP
mysqlsql
mb1987
mb1987

Reputation: 467

updating table by using another table

I have a table called Email which have a column called Email which consist of all emails and another table called peronaldomains which has a column called domains consist of all domains

I want to update a column in Email table called email_type_id which I want to update to email_type_id = 2 for whatever email that has domains matching to domains column in personaldomains table

eg abcd.abc.com, efgh.abc.com, ghik.abc.com in Email column in email table has abc.com in domains columns of personaldomains table

the email table has around 6 million rows and personaldomains table has 1213 rows.

I am using mysql

the query i used to create personaldomains table is

create table personaldomains
SELECT RIGHT(Email, CHAR_LENGTH(RTRIM(Email)) - Locate('@', Email)) Domains ,
COUNT(Email) EmailCount
FROM   email
WHERE  email_type_id = 2 and
CHAR_LENGTH(RTRIM(Email)) > 0
GROUP BY RIGHT(Email, CHAR_LENGTH(RTRIM(Email)) - Locate('@', Email))
ORDER BY EmailCount desc

Upvotes: 0

Views: 51

Answers (1)

GolezTrol
GolezTrol

Reputation: 116100

Since you already have this expression:

RIGHT(Email, CHAR_LENGTH(RTRIM(Email)) - Locate('@', Email))

I think it's something like

UPDATE email e
SET e.email_type_id = 2
WHERE
  EXISTS (
    SELECT 'x' 
    FROM personaldomains pd 
    WHERE pd.domain = 
      RIGHT(e.Email, CHAR_LENGTH(RTRIM(e.Email)) - Locate('@', e.Email)))

Upvotes: 3

Related Questions