CODEWITHSUNDEEP
phpmysqlduplicates
4M8B
4M8B

Reputation: 21

How to remove duplicates correct?

I have a MySQL chart table like this : PRIMARY KEY(ID), Name, Value, Date

I need to remove duplicates if "Name AND Value AND Date" are the same as existing row. I have beneath a solution i found while ago and which worked (not 100%), but I don't understand the command in it's total because I'm only into BASIC MySQL... Can somebody explain me a little further...

definitely what is the x at the end ???

$delDups = "delete from chart where id not in (select * from (select min(id) from chart n group by value) x)";

mysql_query($delDups);

Upvotes: 0

Views: 119

Answers (3)

Mihai
Mihai

Reputation: 26784

You could add an unique key for all 3 columns:

ALTER IGNORE TABLE my_table
ADD CONSTRAINT uc_unic UNIQUE (Name, Value, Date)

As to that x,mysql permit aliases,essentially name shortcuts for convenience.

Upvotes: 1

Sam Mason
Sam Mason

Reputation: 16174

What you wrote will almost work, you just want to add the name and date to the GROUP BY clause. Something like this should do.

DELETE FROM chart
WHERE id NOT IN (
  SELECT MIN(id)
  FROM chart
  GROUP BY name, value, date)

The DELETE FROM says you want to be deleting rows from a table. The WHERE clause says which rows you actually want to delete (missing it out will remove everything). The sub-query in the brackets will look through every combination of name, value and date and give you one id back from each combination. Putting it all together, the DELETE should now drop every row whose id isn't the smallest for each group.

Hope that helps!

Upvotes: 0

D. Melo
D. Melo

Reputation: 2239

It appears to me that you could do it simpler, like this:

$delDups = "delete from chart where id not in (select min(id) from chart n group by value)";

In the subquery you are saing: " Hey, take all the values and find the minimun id for the group of values"

So, imagine the result of the subquery as a list, like "(12, 13, 200)".. the NOT IN operator will take that list and use it to filter the result of the upper query and say "Give me all the results, less the ones where id is in this list"

I'm not sure if I explained it as expected...

Upvotes: 1

Related Questions