Remove duplicate rows from mysql table result

Question

I have a table named consignment which has some duplicate rows against column "service" where service='CLRC'.

select * from consignment where service='CLRC'

When i select the rows, i have total 2023 rows which includes duplicates.

I wrote the below query to delete the rows but i want to select them first to make sure its deleting the correct records.

When the select runs it returns 64431 records. Is that correct?

select t1.hawb FROM consignment t1
INNER JOIN consignment t2 
WHERE 
    t1.id < t2.id AND 
    t1.hawb = t2.hawb
    and t1.service='CLRC'

To delete you will `select t1.id`. If you delete by `hawb` , you will be accidentally deleting all the rows — Madhur Bhaiya, Sep 26 '19 at 16:21
I suppose you have duplicate rows excluding the id column, probably it's primary key, don't you? — Barbaros Özhan, Sep 26 '19 at 16:28
"id" is the primary key which is unique for each row and i have duplicate rows against column "hawb" where " service='CLRC' " — user3855851, Sep 26 '19 at 16:31
Possible duplicate of https://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql — sn n, Sep 26 '19 at 16:33

forpas · Accepted Answer · 2019-09-26T17:32:24.303

1

If you expect your query to return the number of duplicates then no it is not correct.
The condition t1.id < t2.id will join every id of t1 with all ids from t2 that are greater resulting on more rows or less rows (in the case of only 2 duplicates) and rarely in the expected number.
See the demo.
If you want to see all the duplicates:

select * from consignment t
where t.service = 'CLRC' 
and exists (
  select 1 from consignment
  where service = t.service and id <> t.id and hawb = t.hawb 
)

See the demo.
If you want to delete the duplicates and keep only the one ones with the max id for each hawb then:

delete from consignment
where service='CLRC'
and id not in (
  select id from (
    select max(id) id from consignment
    where service='CLRC' 
    group by hawb
  ) t  
);

See the demo.

edited Sep 26 '19 at 17:32

answered Sep 26 '19 at 16:51

forpas

160,666
10
38
76

the select returns 1954 records and when the change the delete keyword to select in the 2nd query it returns 2010 records. Is that fine? – user3855851 Sep 26 '19 at 17:26
the select still returns 2010 rows and the delete returns 1954 records. – user3855851 Sep 26 '19 at 17:46
The remaining 56 rows are the ones with the max id that were not deleted. – forpas Sep 26 '19 at 17:47

Barbaros Özhan · Answer 2 · 2019-09-26T16:50:22.563

Include all the columns in the matching condition except id column, as being primary key :

delete t1 
  from consignment t1
  join consignment t2 
 where t1.id < t2.id 
   and t1.hawb = t2.hawb
   and t1.col1=t2.col1
   and t1.col2=t2.col2   
   ......
   and t1.service='CLRC';

Demo

You can check the number of duplicates by

select count(*) from
(
select distinct hawb, col1, col2, service --  (all columns except `id`)
  from consignment
) q

check whether this number equals number of deleted records just before commiting the changes.

Remove duplicate rows from mysql table result

2 Answers2