homepage Welcome to WebmasterWorld Guest from 54.237.228.144
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Merging Data Tables and removing Duplicates
username




msg:4177672
 4:29 am on Jul 28, 2010 (gmt 0)

Hi all,

I am looking for an efficient means of merging a number of mysql tables into one database table. They all have the same column names, but I want to ensure no duplicates are added to the merged output. Imagine there is an email field, which is the field I want to test duplication on.

I am not sure whether this can be done with pure mysql, but I will be merging a number of table rows (1000's) at one time, and need it to be as light as possible.

The merging with PHP is ok, but the duplicate check seems to be where the resources use increases.

Thanks in advance.

 

dreamcatcher




msg:4177710
 7:19 am on Jul 28, 2010 (gmt 0)

Create a new table and make the e-mail field unique, this will prevent duplicates. Then copy data between tables using IGNORE to skip over any duplicates without errors.

INSERT IGNORE INTO Table2 SELECT FROM Table1;

Think that should work.

dc

username




msg:4177811
 10:44 am on Jul 28, 2010 (gmt 0)

Thanks Dreamcatcher, good suggestion, except that it appears a "TEXT" type field (the email field) in a mysql db table cannot be set as unique for some reason. It appears only numeric values can? Is this correct? Setting a unique value here would work though? Below is the mysql error:



#1170 - BLOB/TEXT column 'email' used in key specification without a key length


Any help would be appreciated.

dreamcatcher




msg:4178390
 7:11 am on Jul 29, 2010 (gmt 0)

Does the e-mail field have to be a text field? I would have thought a varchar(250) would have been plenty. A text field seems overkill.

dc

username




msg:4179007
 6:09 am on Jul 30, 2010 (gmt 0)

Thanks guys, that did it.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
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