brown2005 Posted January 31, 2007 Share Posted January 31, 2007 hi i have a table and i want to be able to delete duplicates say from table a field a.. is there an easy way of doing this? Quote Link to comment https://forums.phpfreaks.com/topic/36557-easy-way-of-deleting-duplicates-in-mysql/ Share on other sites More sharing options...
marcus Posted January 31, 2007 Share Posted January 31, 2007 Well if you're using a script to have it be inserted into the database you could do. $sql = "SELECT * FROM `table` WHERE `field1` ='$field1'"; $res = mysql_query($sql) or die(mysql_error()); if(mysql_num_rows($res) > 0){ //dont insert }else { //insert query } Quote Link to comment https://forums.phpfreaks.com/topic/36557-easy-way-of-deleting-duplicates-in-mysql/#findComment-174071 Share on other sites More sharing options...
utexas_pjm Posted January 31, 2007 Share Posted January 31, 2007 Define "easy"... DELETE FROM table_a WHERE id in ( SELECT id FROM table_a GROUP BY column_a HAVING COUNT(*) > 1 ) Best, Patrick Quote Link to comment https://forums.phpfreaks.com/topic/36557-easy-way-of-deleting-duplicates-in-mysql/#findComment-174073 Share on other sites More sharing options...
brown2005 Posted January 31, 2007 Author Share Posted January 31, 2007 SQL query: DELETE FROM tablea WHERE email IN ( SELECT email FROM tablea GROUP BY email HAVING COUNT( * ) >1 ) MySQL said: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT email FROM tablea GROUP BY email HAVING COUNT(*) > i get that mate Quote Link to comment https://forums.phpfreaks.com/topic/36557-easy-way-of-deleting-duplicates-in-mysql/#findComment-174080 Share on other sites More sharing options...
utexas_pjm Posted January 31, 2007 Share Posted January 31, 2007 Hmmm... Can you post your version of MySQL? I'll try to do this old school with joins, give me a few mins. Patrick Quote Link to comment https://forums.phpfreaks.com/topic/36557-easy-way-of-deleting-duplicates-in-mysql/#findComment-174082 Share on other sites More sharing options...
brown2005 Posted January 31, 2007 Author Share Posted January 31, 2007 MySQL client version: 4.1.11 that bit? Quote Link to comment https://forums.phpfreaks.com/topic/36557-easy-way-of-deleting-duplicates-in-mysql/#findComment-174083 Share on other sites More sharing options...
utexas_pjm Posted January 31, 2007 Share Posted January 31, 2007 Yes, that's what I need. Furthermore let me explain something before I wipe out your data. The SQL I gave you will delete all duplicates in the literal sense. That is, any email which appears more than once will be wiped out. So if the email [email protected] appeared twice, after running the query all records containing that email address would be deleted. I don't think, looking at your query, that is your intention. It seems that you want to delete all subsequent duplicate email addresses, preserving the original. Let me know if this sounds right. Patrick Quote Link to comment https://forums.phpfreaks.com/topic/36557-easy-way-of-deleting-duplicates-in-mysql/#findComment-174088 Share on other sites More sharing options...
brown2005 Posted January 31, 2007 Author Share Posted January 31, 2007 yes, i want to keep one email record, and delete any records with that email.. exactly.. Quote Link to comment https://forums.phpfreaks.com/topic/36557-easy-way-of-deleting-duplicates-in-mysql/#findComment-174090 Share on other sites More sharing options...
utexas_pjm Posted February 1, 2007 Share Posted February 1, 2007 hmmm.... try this, preferably on a dummy table first: DELETE tablea FROM tablea AS a1, tablea AS a2 WHERE a1.email = a2.email AND a1.id > a2.id This query assumes that you have an id column that is sequential. Patrick Quote Link to comment https://forums.phpfreaks.com/topic/36557-easy-way-of-deleting-duplicates-in-mysql/#findComment-174136 Share on other sites More sharing options...
brown2005 Posted February 1, 2007 Author Share Posted February 1, 2007 thanks very much, works perfectly Quote Link to comment https://forums.phpfreaks.com/topic/36557-easy-way-of-deleting-duplicates-in-mysql/#findComment-174585 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.