Quickly Comparing Two MySQL Tables

Forum Moderators: open

Message Too Old, No Replies

Quickly Comparing Two MySQL Tables

Tom_Cash

11:17 am on Jun 11, 2014 (gmt 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.

topr8

2:16 pm on Jun 11, 2014 (gmt 0)

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?

brotherhood of LAN

2:22 pm on Jun 11, 2014 (gmt 0)

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`

Tom_Cash

2:29 pm on Jun 11, 2014 (gmt 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.

topr8

5:58 pm on Jun 11, 2014 (gmt 0)

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

UPDATE table1 SET Active=1 WHERE Email IN (SELECT Email from table2);

Tom_Cash

8:45 am on Jun 12, 2014 (gmt 0)

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