delete duplicates rows in DB

Question

am finding hard to delete duplicates records.

want to delete the records

here is my query

delete from ldataphd.srvalt3 where a3_loan in (select a3_loan from 
ldataphd.srvalt3 group by a3_loan having(*) >1)

above query deleting all the records.

table:

 a3_loan     a3_seq
 1744         1
 1744         32
 1844         1
 1844         35

expected output

a3_loan      a3_seq
 1744          1
 1844          1

Follow this answer: https://stackoverflow.com/a/5016434/3876565 — DineshDB, Mar 28 '18 at 10:32

score 0 · Answer 1 · edited Mar 28 '18 at 10:31

NB - You need to do this first on a test copy of your table!

When I did it, I found that unless I also included AND n1.id <> n2.id, it deleted every row in the table.

1) If you want to keep the row with the lowest id value:

DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name

2) If you want to keep the row with the highest id value:

DELETE n1 FROM names n1, names n2 WHERE n1.id < n2.id AND n1.name = n2.name

I used this method in MySQL 5.1

Not sure about other versions.

Update: Since people Googling for removing duplicates end up here Although the OP's question is about DELETE, please be advised that using INSERT and DISTINCT is much faster. For a database with 8 million rows, the below query took 13 minutes, while using DELETE, it took more than 2 hours and yet didn't complete.

Once you found answer from some other's post, mention their name is a good practice. — DineshDB, Mar 28 '18 at 10:32

score 0 · Answer 2 · edited Feb 03 '19 at 11:08

0

This may help you..!

select distinct * into #tmp From EmpDup
   delete from EmpDup
   insert into EmpDup                
   select * from #tmp drop table #tmp

   select * from EmpDup

Refer to this: http://www.besttechtools.com/articles/article/sql-query-to-delete-duplicate-rows

edited Feb 03 '19 at 11:08

marc_s

732,580
175
1,330
1,459

answered Mar 28 '18 at 10:32

vamsi

329
2
10

score 0 · Answer 3 · answered Mar 28 '18 at 10:35

Try this Code ....

      DELETE FROM MyTable
      LEFT OUTER JOIN (
      SELECT MIN(RowId) as RowId, Col1, Col2, Col3 
      FROM MyTable 
      GROUP BY Col1, Col2, Col3
      ) as KeepRows ON
      MyTable.RowId = KeepRows.RowId
      WHERE
      KeepRows.RowId IS NULL

score 0 · Answer 4 · answered Mar 28 '18 at 10:56

0

DELETE n1 FROM table n1, table n2 WHERE n1.a3_seq > n2.a3_seq AND n1.a3_loan = n2.a3_loan

answered Mar 28 '18 at 10:56

Moinul Islam

469
2
9

While this code may answer the question, it would be better to include some context, explaining how it works and when to use it. Code-only answers are not useful in the long run. – piman314 Mar 28 '18 at 12:32
assume you have two table n1 and n2.you wants to delete rows from n1 if a3_loan contains in n2 and n1.a3_seq is not equal to n1.a3_seq. Now cane to the point. He wants to keep all first a3_seq.so you can delete all rows which contain the same id and not contains same(a3_seq).in this case greater than!! – Moinul Islam Mar 29 '18 at 15:32
@ncfirth .Think a3_seq and a3_loan as a composite key. so you can easily understand. you just need to delete by composite keys because ID is not unique here. – Moinul Islam Mar 29 '18 at 15:41

delete duplicates rows in DB

4 Answers4