niche Posted June 23, 2011 Share Posted June 23, 2011 I'd like to delete duplicate rows in a MYSQL table without creating a new table.The examples I've seen suggest that I should select unique rows with DISTINCT and create a new table. If retaining the old table, while removing duplicate rows is acceptable , how is that done generally? Link to comment Share on other sites More sharing options...
Ingolme Posted June 23, 2011 Share Posted June 23, 2011 Your MySQL tables should always have at least one unique column. If they don't, there's really no way to delete just one duplicate row because there is no way to identify it. You shouldn't ever have to delete duplicate rows, but rather prevent any duplicate rows from being created in the first place. Link to comment Share on other sites More sharing options...
niche Posted June 23, 2011 Author Share Posted June 23, 2011 so, there's nothing in the "manual" that let's me order a primary key and remove the second and subsequent records of a duplicated key by default and reatain all rows with unique keys and only the first row with duplicate keys? Link to comment Share on other sites More sharing options...
Ingolme Posted June 23, 2011 Share Posted June 23, 2011 Edit: Actually, the DELETE statement does have a LIMIT clause. If you know the amount of rows there are then you can set the LIMIT to one less than that.To delete a row, you have to specify the values of the row you want to delete, so that the database engine can find the row and delete it. If two rows have the same values then both of them will be deleted because there is nothing to tell them apart. That's database theory. Link to comment Share on other sites More sharing options...
justsomeguy Posted June 23, 2011 Share Posted June 23, 2011 What's wrong with using a temporary table to do this? It's the fastest and easiest way. You're talking about ordering by primary keys, but a table will not have duplicate primary keys, the database won't allow it. It also won't allow records to have duplicate unique keys. You can duplicate an index that is not unique, but you can't duplicate a primary key or unique key. Link to comment Share on other sites More sharing options...
niche Posted June 23, 2011 Author Share Posted June 23, 2011 OK. I do a SELECT DISTINCT and then a mysql_fetch_array to INSERT each row into a new table.Is there a shortcut script to INSERT a row that matches the format in the new table or do I have to define variables for insertion? Link to comment Share on other sites More sharing options...
justsomeguy Posted June 23, 2011 Share Posted June 23, 2011 No PHP is required:http://www.justin-cook.com/wp/2006/12/12/r...database-table/ Link to comment Share on other sites More sharing options...
niche Posted June 23, 2011 Author Share Posted June 23, 2011 Step 1 failed for me:I turned his:CREATE TABLE new_table as SELECT * FROM old_table WHERE 1 GROUP BY [column to remove duplicates by];CREATE TABLE new_table as SELECT * FROM stk2 WHERE 1 GROUP BY [pic]pic is a unique key.What do you think I need to change? I'm running MYSQL 5.1.33. Link to comment Share on other sites More sharing options...
Ingolme Posted June 23, 2011 Share Posted June 23, 2011 I hope your actual query doesn't have square brackets around "[pic]" Link to comment Share on other sites More sharing options...
niche Posted June 23, 2011 Author Share Posted June 23, 2011 Wondered about that, but just following instructions.I should have adapted.Thanks for your help Ingolme and justsomeguy. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.