Jump to content

removing duplicate rows with MYSQL


niche
 Share

Recommended Posts

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?

Edited by niche
Link to comment
Share on other sites

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

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

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

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

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

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

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...