[SOLVED] Finding and removing duplicate entries with more then 1 column

[Azu]

Let's say I have a database with 10 columns

 

a b c d e f g h i and j

 

I want to select or delete all rows where a c and e are duplicated

 

So

 

a c e

row1: 1 2 3

row2: 1 2 3

 

That would be a duplicate

 

row1: 1 2 3

row2: 1 2 2

Would not be a duplicate

 

And there are a bunch of other columns in it that don't matter.

 

 

Please tell me how I can do this

[bubblegum.anarchy]

Hmm, maybe a select concat(a, c, e) and group by concat(a, c, e) in a subquery returning the id to use in a delete.

[Azu]

Hi.. sorry for noisiness.. but I think I'm doing it wrong.. it seems to be returning all of the rows in the whole table.. most of which are not duplicates..

 

select concat(position_x,position_y,position_z) from creature group by concat(position_x,position_y,position_z);

 

That is the exact query I am using.

 

Thanks for your help so far btw ^^

[bubblegum.anarchy]

That is not a subquery... that is just a query.

[bubblegum.anarchy]

Here, this query returns all the duplicates, triplicates... etc

 

SELECT concat(position_x, position_y, position_z)
     , count(position_x, position_y, position_z) AS duplicate
FROM creature 
GROUP BY concat(position_x, position_y, position_z) 
HAVING count(position_x, position_y, position_z) > 1

[Azu]

Hi, thanks dude! I just have 1 more little problem ^^

Error Code : 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 ' position_y, position_z) AS duplicate

FROM creature

GROUP BY concat(position_x,' at line 2

(0 ms taken)

 

Can you please tell me why it is doing this and how to fix ? I run the query in sqlyog by the way.

[bubblegum.anarchy]

SELECT concat(position_x, position_y, position_z)
, count(concat(position_x, position_y, position_z)) as duplicate
FROM creature 
GROUP BY concat(position_x, position_y, position_z) 
HAVING count(concat(position_x, position_y, position_z)) > 1

[Azu]

Yay thank you it work perfectly now!

[Azu]

Oops one more question please replacing select with delete * doesn't seem to work.. I thought that was all I would have to change to make it get rid of the duplicates lol.. please tell me how I have to change it to remove duplicates.. thanks.. =]

[fenway]

You can't DELETE * -- either specify the table name or nothing at all.

[Azu]

Hehe that's what I meant sorry. I replaced it with delete but it doesn't work it just gives an error :<

 

And I think that even if the delete went through it would mess up the database because it would remove it all not just the duplicates

 

 

E.G. if there is a row and there are two duplicates of it it will delete the 2 duplicates AND the row, thus removing all of that data, instead of just the duplicate?

 

Is there a way to fix this please? And also the error? rror Code : 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 'GROUP BY concat bla bla bla....

It appears when I use delete instead of the select stuff. Not sure why it's happening. :S

[Wildbug]

If you have a column that's guaranteed to be unique, either for the whole table or just among the subset otherwise duplicate rows, then you can do this using temporary tables.  Also, with this method, it can't matter which dupes you delete.

 

1) Create a temporary table

2) Insert unique rows into the table: INSERT INTO temp_table SELECT var1,var2,uniquevar FROM yourtable GROUP BY var1,var2 HAVING COUNT(uniquevar) > 1;

3) Delete offending rows: DELETE yourtable FROM yourtable,temp_table WHERE yourtable.var1=temptable.var1 AND yourtable.var2=temptable.var2 AND yourtable.uniquevar != temptable.uniquevar;

 

(You can't use a subquery because: "Currently, you cannot delete from a table and select from the same table in a subquery." [MySQL manual])

[fenway]

You could probably run a delete with a limit...

[bubblegum.anarchy]

Here is an option with two seperate queries (MAKE A BACKUP FIRST)

 

The first creates a string that contains a set of all the unique ids and the second removes all the record id's that do not match have a match in the set - this way each unique copy is still kept. (IMPORTANT: all the records will be removed if @ids is empty when when the DELETE query is executed)

 

SELECT group_concat(id SEPARATOR ',') AS ids FROM (
SELECT id FROM creature group by concat_ws(',', position_x, position_y, position_z)
) AS derived INTO @ids

DELETE FROM creature WHERE NOT find_in_set(id, @ids);

[Azu]

Thanks everyone!

 

It all works perfect now ^^