Welcome to WebmasterWorld Guest from 54.234.38.8

Forum Moderators: open

Message Too Old, No Replies

Quickly Comparing Two MySQL Tables

     
11:17 am on Jun 11, 2014 (gmt 0)

Junior Member

5+ Year Member

joined:June 26, 2008
posts:175
votes: 0


Hello,

I've looked high and low for a solution that works for me and I've had little luck yet.

Basically, I have a tables with email addresses in.

Table 1 has an 'Active' field which is either 0 or 1.

Table 2 simply has 1 field which is an email address.

If the email address is in table 2, I want to set Active in table 1 to 0.

So far, I've written a quick PHP loop which runs through one table and updates another in the process.

I have 150,000 email addresses to run through - this isn't working well...

Does anyone have any recommendations for a speedy solution to the problem?

Apologies if something similar has been answered before...

Tom.
2:16 pm on June 11, 2014 (gmt 0)

Senior Member

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Apr 19, 2002
posts:3171
votes: 8


your question makes no sense...

table 2 has only 1 field ... which contains an email address?

table 1 has an active field, with a value of 1 or 0

what other fields does it have, i'm assuming it has an email field as well is that correct?
2:22 pm on June 11, 2014 (gmt 0)

Moderator from GB 

WebmasterWorld Administrator brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Jan 30, 2002
posts:4842
votes: 1


First off you'll want to add a unique index on table2, since you haven't mentioned any other fields you may as well make it the primary key.

Then something like

UPDATE table1 AS t1
LEFT JOIN table2 AS t2 ON t1.emailaddress = t2.emailaddress
SET t1.active = 1
WHERE t2.emailaddress IS NOT NULL

The left join means that all rows in t1 are returned, but t2.emailaddress is "NULL" where there is not a matching email address in the second table. You can use that to define the value of `active`
2:29 pm on June 11, 2014 (gmt 0)

Junior Member

5+ Year Member

joined:June 26, 2008
posts:175
votes: 0


Apologies for my poorly written question. Let me be more clear:

Table 1: ID, Email, Active
Table 2: Email

Table 2 is a temporary table for helping me process the bulk of table 1. The email address in table 2 is an index.

Thank you for seeing past that, BoL. I'm going to have a crack at implemeting something similar to that.
5:58 pm on June 11, 2014 (gmt 0)

Senior Member

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Apr 19, 2002
posts:3171
votes: 8


MySQL now also supports subqueries ... so you could also do something like

UPDATE table1 SET Active=1 WHERE Email IN (SELECT Email from table2);
8:45 am on June 12, 2014 (gmt 0)

Junior Member

5+ Year Member

joined:June 26, 2008
posts:175
votes: 0


Blimey, I think I need some training... I know nothing about SQL sub-queries. Off to do some reading I go! Thanks Topr8.