delete identical records

[[deleted]]

Is there any way that I can have mysql search for (and delete) any record that is identical to another record in every field but one?

The user imports records and they are assigned an auto incremented primary key. If the user chooses to import again, there are often duplicate records (primary key excluded) that I want to delete. Any ideas?

Thanks
Mark

[[deleted]]

Perhaps I could prevent this from happening in the first place by selecting a record that has all values of the record that is to be entered and only performing the insert of the record doesn't exist. This seems time consuming though. Is there an insert if unique command?

[tasistro]

They are two problems. If you have an empty set then your second post is the way to go. If the damage is already done then:

SELECT * from %table% order by %all fields should be listed here by comma separation%

with php do a while loop comparing all fields if all fields compare then
$repeats[]=$myrow["%your primary key here,usually ID"];

when the while loop breaks do

DELETE from %table% where
%primary_key%=$myrow[0] or
%primary_key%=$myrow[1] or
%primary_key%=$myrow[2] or
%primary_key%=$myrow[3] or
...
%primary_key%=$myrow[N]

Where N is count($repeats)-1

The delete sql will have to be done through a while loop too.

Saludos
Gerardo

[[deleted]]

If you don't have a primary key in the data that you are importing, then you have a problem, because there is no way to tell wether the data is actually a duplicate, or wether two records just happen to be the same.

[[deleted]]

Well, the table that is being updated does contain a primary key which is an auto incremented intiger. The incoming date is derived from a text document. One of the fields in the table is the username of the importer. There will never be an instance where the importer will want to import a record that contains his/her username and already exists.

With that said, checking to see if the record exists (minus the record id) should do the trick.

Does this sound alright?

//insert the text file into temp table
select from temp_table;
// begin fetch loop {
$doit=0;
select from main_table where (all fields but id) = (value of data about to go in) limit 1;
//start the fetch array {
$doit=1;
}// end test loop
if (!($doit==1)) {
// insert the data
}// end of insert
}// end of main loop

[tomhath]

You should be able to fill the temp table with something like this (sorry didn't test). That will give you the duplicate values and the key you want to keep, then delete the dupes.

insert into temptable
select min(keycol), othercols from mytable
group by othercols
having count(*) > 1

delete mytable
from mytable inner join temptable
where mytable.othercols = temptable.othercols
and mytable.keycol <> temptable.keycol

[[deleted]]

If you are 100% sure that no records will ever be dupicated on purpose, then you can just create a unique index on all the columns except the ID column.
Then the table will not accept duplicates in the first place.

To get your duplicates out just create a new table that already has the unique index, and copy the data from the old table to the new table. That will strip all the duplicates. Then empty the old table, add the index, and copy the data back (or drop the old table and rename the new table to the old name)

[[deleted]]

Having a unique index on each field would mean that each field, standing by itself, couldn't have duplicate data. I'm just concerned about duplicate data, collectively, amongst a group of fields. For example, if this was a bookstore database, I would want to allow many books by O'reilly but only one Running Linux by O'Reilly.

Currently, I have it select author=O'reilly and title=running linux first and if it returns nothing, I'll allow the insert. This works but the actual database checks it against 10 fields and typically inserts a block of about 200 records. I just want to make sure that this is the fastest and most efficient way to do this in case the site generates some heavy traffic in the future.

On a side note, every select, insert and update will have the username included and there will never be an instance where the user will select records that aren't his or her own. With that said, is it wise to add an index/key to the username field in the table.
Thanks everyone for your help,
Mark

[[deleted]]

"Having a unique index on each field would mean that each field"

No, create a unique index on all columns together. Then all fields can contain as many duplicates as they want, but the combination of all fields is unique.

[[deleted]]

Thanks once again, Vincent-

I didn't know you could create a unique index on a group of coloms. I have no idea how this is done but I can look it up myself. Thanks for the good advice.
Mark