Removing duplicate values from sql server on condition of 2 columns

Question

|Rownumber  |OldIdassigned  |commoncode  |
------------------------------------------
| 1         |FLEX           |Y2573F102   |
------------------------------------------
| 2         |RCL            |Y2573F102   |
------------------------------------------
| 3         |FLEX           |Y2573F102   |
------------------------------------------
| 4         |QGEN           |N72482123   |
------------------------------------------
| 5         |QGEN           |N72482123   |
------------------------------------------
| 6         |QGEN           |N72482123   |
------------------------------------------
| 7         |RACE           |N72482123   |
------------------------------------------
| 8         |CLB            |N22717107   |
------------------------------------------
| 9         |CLB            |N22717107   |
------------------------------------------
<b>| 10     |CLB            |N22717107   |

I need to delete the duplicate records based on Common code and a condition that - if oldidassigned is same then delete else don't delete.

For example Y2573F102 has 3 duplicate records rows 1,2,3 .... 1,2 need not to be deleted , only 3rd row has to be deleted.

Gordon Linoff · Accepted Answer · 2018-09-26 12:47:00Z

5

I like updatable CTEs and window functions for this purpose:

with todelete as (
      select t.*,
             row_number() over (partition by commoncode order by rownumber) as seqnum
      from t
     )
delete todelete
    where seqnum > 1;

edited Sep 26, 2018 at 12:47

answered Sep 26, 2018 at 11:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Gordon Linoff Over a year ago

@chadalavadaharish . . . I misread the question. You state the condition is on "two columns". But actually, you simply want the oldest record for the third column. This is fixed by removing the second column form the order by.

Yogesh Sharma · Accepted Answer · 2018-09-26 12:36:57Z

2

Use ROW_NUMBER() :

DELETE t
FROM (SELECT t.*, ROW_NUMBER() OVER (PARTITION BY OldIdassigned, commoncode ORDER BY rownumber) AS Seq
      FROM table t
     ) t
WHERE t.seq > 1;

EDIT : If you want to check the duplication based on commoncode only then remove OldIdassigned from PARTITION clause :

DELETE t
FROM (SELECT t.*, ROW_NUMBER() OVER (PARTITION BY commoncode ORDER BY rownumber DESC) AS Seq
      FROM table t
     ) t
WHERE t.seq > 1;

edited Sep 26, 2018 at 12:36

answered Sep 26, 2018 at 11:20

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

1 Comment

Yogesh Sharma Over a year ago

@chadalavadaharish. . . I think you just need commoncode in partition clause.

Zaynul Abadin Tuhin · Accepted Answer · 2018-09-26 12:38:25Z

2

use window function row_number, according to your description and comments it seems you need change in partition clause

delete t
from
(select t1.*,row_number() over(partition by commoncode order by  Rownumber) rn from table t1
)t where rn<>1

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=eacc0688efb534a0addee68678f323fe

edited Sep 26, 2018 at 12:38

answered Sep 26, 2018 at 11:20

Zaynul Abadin Tuhin

32.1k6 gold badges38 silver badges67 bronze badges

2 Comments

Zaynul Abadin Tuhin Over a year ago

@chadalavadaharish dbfiddle.uk/… check this link

Zaynul Abadin Tuhin Over a year ago

@chadalavadaharish does this your output Rownumber OldIdassigned commoncode 1 FLEX Y2573F102 4 QGEN N72482123 8 CLB N22717107

Fahmi · Accepted Answer · 2018-09-26 12:57:58Z

2

Use Row_Number()

delete t from 
(select *, row_number() over(partition by commoncode order by 
 rownumber) as rn) t 
 where rn<>1

edited Sep 26, 2018 at 12:57

answered Sep 26, 2018 at 11:21

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Comments

SQL_M · Accepted Answer · 2018-09-26 11:35:40Z

1

Since all answers are similar (and correct), I will post one alternative way:

DELETE FROM TableA
WHERE EXISTS ( SELECT * FROM TableA AS A2
               WHERE A2.commoncode = TableA.commoncode
                 AND A2.OldIdassigned = TableA.OldIdassigned
                 AND A2.Rownumber < TableA.Rownumber )

answered Sep 26, 2018 at 11:35

SQL_M

2,4852 gold badges18 silver badges32 bronze badges

Collectives™ on Stack Overflow

Removing duplicate values from sql server on condition of 2 columns

5 Answers 5

1 Comment

1 Comment

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related