-1

I have table which has only two columns:

  1. Message_id
  2. Id (primary key)

I want to delete the duplicate from the table but keep the first occurrence or any one occurrence of the row.

The duplicate is determined by using Message_id.

I use this query to find all the duplicate values

(select message_id
 from myDb.My_Tb
 group by message_id 
 having count(message_id) > 1)

I used this below query to get all the row_numbers but the problem is if I create this as another table and use MOD(r,2) = 0 . It doesn't gives me the exact 2nd row but a random 2nd row.

(select row_number() over() as rn, id, message_id 
 from MyDb.My_Tb
 where message_id in (select message_id
                      from MyDb.My_Tb
                      group by message_id 
                      having count(message_id) > 1)
 order by message_id asc)

Thanks for the responses but I was able to do this with this query

delete from MyDb.My_Tb where id in
(select id  from 
(select row_number () over() as rownum, id , message_id
from

(
    (select id ,  message_id from MyDb.My_Tb
     where message_id in

        (select message_id
        from MyDb.My_Tb 
        group by message_id 
        having count(message_id)>1)

     order by message_id asc) 
) as dummy
) as dummy1

where mod(rownum,2) = 0)

because in my case I had only duplicates in numbers of two. Is there a way that we can generalise this for 'N' duplicates?

That Coder
  • 11
  • 4
  • This question is asked every day, no one seems to google that. There are lots of duplicates, one of them here: [How can I remove duplicate rows?](https://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows) – Jonas Metzler Dec 21 '22 at 05:46

1 Answers1

0

Use this SQL statement to delete all the duplicate records except one:

DELETE FROM myDb.My_Tb
WHERE id NOT IN (
  SELECT MIN(id)
  FROM myDb.My_Tb
  GROUP BY Message_id
)

Or you can use:

DELETE FROM myDb.My_Tb t1
WHERE EXISTS (
    SELECT *
    FROM myDb.My_Tb t2
    WHERE t1.Message_id = t2.Message_id AND t1.id > t2.id
);
Raed N.
  • 172
  • 11