CODEWITHSUNDEEP
sqlsql-server
akd
akd

Reputation: 6758

How to delete duplicates with condition in SQL

I have a table like below

CompanyNumber   Name  Status     other column1    other Column2
     10           A     Active
     10           A      NULL
     11           B     Active
     11           B       NULL
     12           C     Active
     12           C        NULL
     13           D        NULL
     14           E      Active


     ...

So over 300 000 rows like this.

I would like to delete the one that has status NULL and the resulting table should be like below :

      CompanyNumber    Name    Status
         10              A       Active
         11              B       Active
         12              C       Active
         13              D       NULL
         14              E       Active

Upvotes: 0

Views: 74

Answers (4)

mh__
mh__

Reputation: 376

You can use an exists clause in your where statement to find null lines with duplicates:

delete
from   tableA
where  tableA.Status is null
and    exists (
           select *
           from   tableA t2
           where  t2.CompanyNumber = tableA.CompanyNumber 
           and    t2.Name = tableA.Name and not t2.Status is null
       )

Upvotes: 0

Dhaval
Dhaval

Reputation: 2379

if you want retain first record and delete other duplicate record then Check this...

   select *,
row_number() over  (partition by CompanyNumber   order by CompanyNumber)as rno 
into  #temp  from table 

  delete * from 
  table 
 where CompanyNumber in
 (select CompanyNumber  from #temp where rno != 1)

Upvotes: 0

M.Ali
M.Ali

Reputation: 69524

;WITH CTE AS 
( 
 SELECT CompanyNumber
      ,Name 
      ,[Status]
      ,ROW_NUMBER() OVER (PARTITION BY CompanyNumber, Name ORDER BY [Status] DESC) rn 
 FROM @TABLE
)
DELETE FROM CTE WHERE rn > 1

Upvotes: 2

SMA
SMA

Reputation: 37023

I get that you want to delete row where status is null. Try SQL with where clause like

DELETE
FROM mytable
WHERE status is null

If you wish to remove only duplicate rows then you could do something like:

DELETE
FROM mytable
WHERE status is null
AND CompanyNumber  IN (SELECT CompanyNumber 
                       FROM mytable
                       GROUP BY CompanyNumber 
                       HAVING COUNT(CompanyNumber) > 1)

Upvotes: 2

Related Questions