Welcome to WebmasterWorld Guest from 54.162.93.137

Forum Moderators: open

Message Too Old, No Replies

Quickly Comparing Two MySQL Tables

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

5+ Year Member



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 Jun 11, 2014 (gmt 0)

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



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 Jun 11, 2014 (gmt 0)

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



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 Jun 11, 2014 (gmt 0)

5+ Year Member



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 Jun 11, 2014 (gmt 0)

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



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 Jun 12, 2014 (gmt 0)

5+ Year Member



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

Featured Threads

Hot Threads This Week

Hot Threads This Month