SQL query to remove multiple duplicate entries from table

Question

I have table containing following entries

Id  |   Accno  |    Name   |    Hash
----+----------+-----------+---------
 1  |    11    |     ABC   |    01110
 2  |    11    |     ABC   |        
 3  |    22    |     PQT   |        
 4  |    33    |     XYZ   |    03330
 5  |    44    |     LMN   |    04440
 6  |    33    |     XYZ   |

I need SQL query to remove duplicate entry from table and keep atleast single entry in table whose hash value is present. and for those entries which are not duplicate should also remain in table.

Please be more specific in what you want, provide us with example — CMPS
– CMPS, Commented Jun 28, 2014 at 5:33
Which columns should it be checking for duplicates? Accno, Name, or both together? — Barmar
– Barmar, Commented Jun 28, 2014 at 5:36
Write a SELECT query that returns all the rows you want to keep. Then use DELETE FROM YourTable WHERE id NOT IN (SELECT id FROM (<subquery>)), where <subquery> is the query you wrote. — Barmar
– Barmar, Commented Jun 28, 2014 at 5:38
Can you give expected output of given data so we can clarify what you want? — Sadikhasan
– Sadikhasan, Commented Jun 28, 2014 at 5:47
I want output as Id | Accno | Name | Hash ----+----------+-----------+--------- 1 | 11 | ABC | 01110 3 | 22 | PQT | 4 | 33 | XYZ | 03330 5 | 44 | LMN | 04440 — Pooja
– Pooja, Commented Jun 28, 2014 at 6:23

Phrancis · Accepted Answer · 2014-06-28 07:30:52Z

1

I think you guys overcomplicate things a lot. This should work just dandy:

DELETE FROM 
    YourTable
WHERE Hash IS NULL
AND Accno IN 
    (
    SELECT Accno
    FROM YourTable
    GROUP BY Accno
    HAVING COUNT(Name) > 1
    )
;

edited Jun 28, 2014 at 7:30

answered Jun 28, 2014 at 6:15

Phrancis

2,2902 gold badges29 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Phrancis Over a year ago

Thanks Dagg Nabbit for the suggested corrections.

iceyb · Accepted Answer · 2014-06-28 05:31:49Z

0

Probably the easiest way to do it is to create a new table and copy non duplicate entries.

create table table_name2 as select distinct * from table_name1;
drop table table_name1;
rename table_name2 to table_name1;

Something like this.

answered Jun 28, 2014 at 5:31

iceyb

4364 silver badges21 bronze badges

2 Comments

Barmar Over a year ago

This will copy all the rows, because the id column makes them all distinct. He also said he wants to keep the row with a non-empty Hash column.

Pooja Over a year ago

I want to remove duplicate entries of accno and keep those entries for which hash is present and in case hash is not present for any unique entry keep that too in database

Sadikhasan · Accepted Answer · 2014-06-28 06:41:09Z

0

Create table temp2 as SELECT *
FROM temp where id in (select id from temp group by accno having count(accno)>=1 and hash<>'');    
drop table old_table;
rename table temp2 to old_table;

Check SQL Fiddle

edited Jun 28, 2014 at 6:41

answered Jun 28, 2014 at 5:56

Sadikhasan

18.6k23 gold badges85 silver badges126 bronze badges

Collectives™ on Stack Overflow

SQL query to remove multiple duplicate entries from table

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related