Need to delete all duplicate row in database if two column data are same in Mssql

Question

Hi i have a table named Credits like this

ID identity
Name varchar
CustCode varchar
ConsignorNo varchar
Address varchar

However i have duplicate rows throughout database like this

ID 1  Name John  CustCode  A1   ConsignorNo  BBS   Address  XXX
ID 2  Name Mari  CustCode  A2   ConsignorNo  NRM   Address  XXX
ID 3  Name John  CustCode  A1   ConsignorNo  BBS   Address  XXX
ID 4  Name Mari  CustCode  A2   ConsignorNo  MMS   Address  XXX

Since the CustCode and ConsignorNo are same in John so it must be deleted and Mari should not be deleted as it contains same custcode but different ConsignorNo. I have tried this but throws error that transaction log is full. The query is

delete from Credits 
where ID not in (select MAX(ID) 
                 from Credits 
                 group by ConsignerNo, CustomerCode)

Do you want to delete all or only so many that there is one row left? — Tim Schmelter, Jan 13 '15 at 08:51
if you refer to my comment, my answer showns both way, the first approach leaves one row whereas all others are deleted. — Tim Schmelter, Jan 13 '15 at 10:08

score 0 · Accepted Answer · answered Jan 13 '15 at 08:53

0

One way, using a CTE + ROW_NUMBER:

WITH CTE AS
(
    SELECT t.*, 
           RN = ROW_NUMBER() OVER (PARTITION BY Custcode, ConsignorNo ORDER BY ID DESC)
    FROM dbo.tableName
)
DELETE FROM CTE
WHERE RN > 1

I like it because it's simple and can be changed easily to see what you're going to delete.

Above deletes all but one row(the one with the highest ID). If you want to delete all which have duplicates(it's not that clear) then you can use COUNT(*)OVER(PARTITION BY Custcode, ConsignorNo):

WITH CTE AS
(
    SELECT t.*, 
           Count = COUNT(*) OVER (PARTITION BY Custcode, ConsignorNo)
    FROM dbo.tableName
)
DELETE FROM CTE
WHERE Count > 1

answered Jan 13 '15 at 08:53

Tim Schmelter

450,073
74
686
939

The query is showing error after couple of minutes. The error is The transaction log for database '1strackcourier_courier' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases – Ranjancode Jan 14 '15 at 11:27
@user1862373: if you replace `DELETE FROM CTE WHERE RN > 1` with `SELECT * FROM CTE WHERE RN > 1`, how many rows are returned? Those are the records which will be deleted. According to the error have a look here: http://stackoverflow.com/questions/13421370/the-transaction-log-for-database-databasename-is-full Maybe it's simply your hard drive that runs out of disk space. The transaction log is used for example to rollback all changes. If you delete many records your creating a big translaction log. – Tim Schmelter Jan 14 '15 at 11:34
Thanks. No need of delete all the things. if you give me a query for getting records without duplicates, then it will be very good. I need also a where condition for the custcode. – Ranjancode Jan 14 '15 at 12:09
@user1862373: as mentioned you just need to replace `DELETE..` with `SELECT..` and you get what you want. – Tim Schmelter Jan 14 '15 at 12:18
It is working and removing one row only from duplicates. If the rows having 4 duplicates then it is showing 3 rows of duplicates. – Ranjancode Jan 14 '15 at 12:58
@user1862373: sorry, i don't understand that comment. Is it the correct result? And what does it mean at all? The sql deletes **all** duplicates apart from one for each `Custcode, ConsignorNo` group. – Tim Schmelter Jan 14 '15 at 13:04
i have replaced delete to select and it is showing data correctly but i have 4 rows of same custcode and consignorno but it showing 3 rows of same. i need to show only one row of same custcode and consignorno – Ranjancode Jan 14 '15 at 13:14
@user1862373: if you don't want to see the duplicates, so those which would be deleted, use `WHERE RN = 1` instead of `WHERE RN > 1`. Then you get only the non-duplicate records. – Tim Schmelter Jan 14 '15 at 13:16

score 0 · Answer 2 · answered Jan 13 '15 at 08:55

0

Try some thing like this:

   delete from Credits 
    where ID = (select MAX(ID) 
                     from Credits 
                     group by ConsignerNo, CustomerCode having count(*)>1)

answered Jan 13 '15 at 08:55

Krupa Patel

3,309
3
23
28

Hope this may help you! – Krupa Patel Jan 13 '15 at 08:56
It throws error that is Subquery returned more than 1 value – Ranjancode Jan 14 '15 at 11:32

Need to delete all duplicate row in database if two column data are same in Mssql

2 Answers2