0

I have a table with duplicates (COL2) within the same category (defined in COL1), when I try to delete tuples within COL2, I can't find a way to do this only within the same category, instead it deletes tuples everywhere in the table.

Here is the current table T1 BEFORE removing duplicates:

        COL1      COL2
         A         1
         A         2
         A         2
         A         2
         B         1
         B         2
         B         2
         B         3


Here is the current table T1 AFTER removing duplicates:

        COL1      COL2
         A         1
         A         2
         B         3

Here is what I should have in T1:
        COL1      COL2
         A         1
         A         2
         B         1
         B         2
         B         3

Here is the code I used to delete duplicates:

    CREATE TABLE TEMP LIKE T1;
    INSERT INTO TEMP SELECT * FROM T1 WHERE 1 GROUP BY COL2;
    DROP TABLE T1;
    ALTER TABLE TEMP RENAME T1;

Any idea how to tell mysql to delete tuples only within a category of COL1?

madkitty
  • 1,657
  • 6
  • 24
  • 37
  • Where is what you should have in T1? – Barmar Aug 29 '14 at 21:39
  • possible duplicate of [Delete all Duplicate Rows except for One in MySQL?](http://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql) another one [Remove duplicate rows in MySQL](http://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql) – M Khalid Junaid Aug 29 '14 at 21:52

1 Answers1

1

Use both columns in your GROUP BY clause:

INSERT INTO TEMP
SELECT *
FROM T1
GROUP BY COL1, COL2
Barmar
  • 741,623
  • 53
  • 500
  • 612