Remove SQL duplicates

Question

I'm totally new in SQL. I never used it and just need a simple answer because I don't have time to learn SQL right now :(. I need to remove duplicated records from my local DB. Case looks like this:

| id | type  | ... |
-------------------
| 1  | test  | ... |
| 1  | test2 | ... |
| 1  | test  | ... |
| 1  | test  | ... |

I want to remove all duplicated record which has the same id and type but leave only on record. Like this:

| id | type  | ... |
-------------------
| 1  | test  | ... |
| 1  | test2 | ... |

Using delete by Id is impossible. I have 50k records and I want to remove all duplicated records. When ID and Type are the same.

What data is in the other columns, if those columns even exist? What is the logic for _which_ record from each `id/type` combination gets retained? — Tim Biegeleisen, Mar 05 '19 at 10:33
Also what you want is not possible, your current data example does not have a order which we can use to order. — Raymond Nijland, Mar 05 '19 at 10:48
Please define which rows you want to keep, and how you can identify them. — Paul Spiegel, Mar 05 '19 at 11:22
https://stackoverflow.com/questions/36647058/removing-duplicates-with-unique-index — Paul Spiegel, Mar 07 '19 at 11:42

score 1 · Answer 1 · answered Mar 05 '19 at 10:39

1

Please try this

First Way

 SELECT id, type
        FROM table_name
  Group by id, type

Second Way

 SELECT DISTINCT id, type
        FROM table_name;

answered Mar 05 '19 at 10:39

Hemang A

1,012
1
5
16

score 0 · Answer 2 · answered Mar 05 '19 at 10:34

0

SELECT DISTINCT statement is used to return only distinct (different) values.

Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

SELECT DISTINCT column1, column2, ...
FROM table_name;

In your table

 SELECT DISTINCT id, type, ...
    FROM table_name;

answered Mar 05 '19 at 10:34

Fatikhan Gasimov

903
2
16
40

(1, test, a) and (1, test, b) are distinct. But OP wants to keep only one of them. – Paul Spiegel Mar 05 '19 at 11:25

score 0 · Answer 3 · answered Mar 05 '19 at 10:37

0

you just need to use the keyword distinct when selecting mate.. try like this

 SELECT DISTINCT id, type, blah blah blah FROM your_table; // this should take care of em

answered Mar 05 '19 at 10:37

ALPHA

1,135
1
8
18

Amir Molaei · Accepted Answer · 2019-03-05T11:00:45.363

0

A TSQL sample code that might help:

 WITH tbl_alias AS
    (
       SELECT id, type,
           RN = ROW_NUMBER()OVER(PARTITION BY id, type ORDER BY id)
       FROM tbl
    )
    DELETE FROM tbl_alias WHERE RN > 1

Also you can try How to delete duplicates on a MySQL table? .

edited Mar 05 '19 at 11:00

answered Mar 05 '19 at 10:38

Amir Molaei

3,700
1
17
20

MySQL 8 supporfs this only – Raymond Nijland Mar 05 '19 at 10:45
Yes it works, maybe temp is not a suitable name. I changed it to tbl_alias – Amir Molaei Mar 05 '19 at 11:04
["ER_NO_SUCH_TABLE: Table 'test.tbl_alias' doesn't exist"](https://www.db-fiddle.com/f/wCuYEZyEQGVy3Eoq1jdZAC/0) – Paul Spiegel Mar 05 '19 at 11:10
It's a tsql code and I tested it on Sql Server. Although the question is tagged with MySql, I just provided it in case it could be a help. – Amir Molaei Mar 05 '19 at 11:15
OK.. verified on SQL-Server. Strange feature. – Paul Spiegel Mar 05 '19 at 11:20

kiks73 · Answer 5 · 2019-03-05T13:53:51.543

You should replace your table grouping by id and type, and using an aggregate function on the other fields.

You should add to your question the definition of your table and specify the rule to use to get the other fields. Anyway, this is a simple solution:

-- Create a temp copy of original table with distinct values
CREATE TEMPORARY TABLE copy_table1 
   SELECT id, type, MIN(field3) AS field3, ...
   FROM table1
   GROUP BY id, type;

-- Empty original table
DELETE FROM table1;

-- Insert distinct data into original table
INSERT INTO table1 (id, type, field3, ...) 
    SELECT id, type, field3, ...
    FROM copy_table1;

Remove SQL duplicates

5 Answers5