homepage Welcome to WebmasterWorld Guest from 54.197.215.146
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Quickly Comparing Two MySQL Tables
Tom_Cash




msg:4679054
 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




msg:4679095
 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




msg:4679096
 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




msg:4679097
 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




msg:4679139
 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




msg:4679263
 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved