CODEWITHSUNDEEP
sqlsql-serverduplicatessubquerysql-delete
Az Lateef
Az Lateef

Reputation: 1

Deleting duplicates from same SQL table

My table looks like this (in SQL Server 2016 SP2):

KEY | CN_ID | CNO_IND
----+-------+--------
001 | P001  | N
002 | P001  | Y
003 | P001  | N
005 | P002  | N
006 | P002  | N
007 | P004  | N
008 | P004  | Y
009 | P004  | N
010 | P004  | Y

Here's what I want to do:

For all CN_ID with CNO_IND = 'Y', I want to delete all the CN_ID with CNO_IND = 'N'.

My final result will be like this:

KEY | CN_ID | CNO_IND
----+-------+--------
002 | P001  | Y
005 | P002  | N
006 | P002  | N
008 | P004  | Y
010 | P004  | Y

As you can see, P002 will not be deleted since none of P002 has CNO_IND = 'Y'. P004 still has duplicates since I want to retain all records with CNO_IND = 'Y' and remove duplicates with CNO_IND = 'N'.

Hope to find the best solution for this.

Thanks in advance

Upvotes: 0

Views: 61

Answers (3)

astentx
astentx

Reputation: 6751

But you still can do it with CTE:

With del as (
  Select
    [KEY], CN_ID, CNO_IND,
    max(case CNO_IND when 'Y' then 'Y' end) over (Partition by CN_ID) as has_y
  from tab
)

delete
from del
where has_y = 'Y'
  and cno_ind = 'N'

Fiddle

In your example result you've left duplicates for P002 which has only N despite your need to delete duplicated N's, so I didn't delete duplicates of N with only N too.

Upvotes: 0

Nits Patel
Nits Patel

Reputation: 390

You can use CTE Something like this

With CTE as (
Select KEY, CN_ID, CNO_IND, Row_Number() over (Partition by CN_ID  Order By CN_ID) as rnk from YourTable
)

Delete from CTE where rnk > 1

Upvotes: 0

GMB
GMB

Reputation: 222592

One option uses exists:

delete t
from mytable t
where cno_ind = 'N' and exists (
    select 1 from mytable t1 where t1.cn_id = t.cn_id and t1.cno_ind = 'Y'
)

Upvotes: 2

Related Questions