How to delete duplicated record?

Question

I have SQL table with duplicated record but one filed is different
How can Delete duplicated record which this filed is null ?
and if two pair is null delete one

my table is :
id Number No
1 A25 10
2 A24 20
3 A25  
4 C12
5 C12

new table : 
id Number No 
1 A25 10 
2 A24 20 
4 C12
5 C12

This question has several duplicates [1](http://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql), [2](http://stackoverflow.com/questions/1651999/mysql-remove-duplicates-from-big-database-quick) and [3](http://stackoverflow.com/questions/2630440/how-to-delete-duplicates-on-mysql-table). You should research before asking question. — IROEGBU, Apr 17 '15 at 08:52
There are no duplicated rows... Do you mean duplicated values in the column Number? What if several rows with same Number, both with non-null No values? — jarlh, Apr 17 '15 at 09:13

score 2 · Answer 1 · answered Apr 17 '15 at 08:45

2

Try this.

select *
into    #temp1
from    My_table 
group by Number
having Count(*)>1

delete  My_table 
from    My_table 
join    #temp1
    on   My_table.Number = #temp1.Number
where    My_table.Number is null

answered Apr 17 '15 at 08:45

Ambareesh Surendran

508
2
12

Igor Gorjanc · Accepted Answer · 2015-04-20T07:21:52.610

2

delete from mytable where id in(
    select t1.id
    from mytable t join mytable t1 on t.number=t1.number and t1.id>t.id
    where (t1.no is null and t.no is not null) or (t1.no is null and t.no is null)
)

edited Apr 20 '15 at 07:21

answered Apr 17 '15 at 08:59

Igor Gorjanc

535
4
17

Correct, platform-independent and without temporary tables, well done. But is the DISTINCT required? – Twinkles Apr 17 '15 at 09:09
i can't use this query , i have one table what is t and t1 ? – Hadi Ranji Apr 17 '15 at 13:41
Handi: you need to replace `mytable` (3 times!) with your actual table name. `t` and `t1` are just aliases. You don't need to change them. ( http://www.w3schools.com/sql/sql_alias.asp ) What error do you get? – Igor Gorjanc Apr 17 '15 at 14:29
Twinkles: No, DISTINCT is actually not required, because we use DELETE statement. But it would be required in case of SELECT statement. – Igor Gorjanc Apr 17 '15 at 14:45
but when pair is null , not deleted any one ! – Hadi Ranji Apr 17 '15 at 14:56
sorry not working , i changed t1.number>t.number to t1.number=t.number and deleted many records – Hadi Ranji Apr 17 '15 at 15:20
Handi: sorry for a typo. Try now. – Igor Gorjanc Apr 20 '15 at 07:23

score 1 · Answer 3 · answered Feb 12 '16 at 08:48

1. solution

delete from emp
    where rowid not in
    (select max(rowid) from emp group by empno);

2. sloution

delete from emp where rowid in
               (
                 select rid from
                  (
                    select rowid rid,
                      row_number() over(partition by empno order by empno) rn
                      from emp
                  )
                where rn > 1
               );

3.solution

delete from emp e1
         where rowid not in
          (select max(rowid) from emp e2
           where e1.empno = e2.empno );

4. solution

 delete from emp where rowid in
            (
             select rid from
                (
                  select rowid rid,
                  dense_rank() over(partition by empno order by rowid
                ) rn
             from emp
            )
 where rn > 1
);

How to delete duplicated record?

3 Answers3