Delete single record from duplicate records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Delete single record from duplicate records

Author

Topic

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-10-25 : 04:35:40

Dear All,

In my table i am having duplicate records, I want to delete single record from the duplicate records.

Can anybody please tell me the SQL statement for this ?

Thanks and Regard's
Harish Patil

Cindyaz
Yak Posting Veteran

73 Posts

Posted - 2011-10-25 : 04:40:11

Please post table structure and sample data.

delete from mytable where id in (select min(id) from mytable group by mycolumn having count(*)>1)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-25 : 04:40:15

can you show some sample data and then tell what according to you represents duplicate? also give what output you're expecting among them after the deleton.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-10-25 : 04:49:56

DECLARE @Employee TABLE (EmpID INT,EmpSSN CHAR(9))

INSERT INTO @Employee
SELECT 1,'895698121'
UNION ALL
SELECT 2,'877454411'
UNION ALL
SELECT 3,'895698121'

SELECT * FROM @Employee

;WITH CTE
AS(
SELECT *,ROW_NUMBER() OVER (PARTITION BY EmpSSN ORDER BY EmpID)AS ROWNUMBER FROM @Employee)

DELETE FROM CTE
WHERE ROWNUMBER > 1

SELECT * FROM @Employee

--------------------------
http://connectsql.com/

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-10-25 : 04:56:34

Hello Friend,

Following is the sql statement i am using for finding the duplicate records.

SELECT
CR.Country_Code As CountryCode,
CR.Dialed_Digit As AreaCode,
COUNT(*)
FROM tb_l_r_d RD
INNER JOIN tb_l_r_d_d DT ON DT.Rate_Deck_ID = RD.ID
INNER JOIN tb_l_r_d_f RDF ON RDF.Rate_Deck_Details_ID = DT.ID
INNER JOIN tb_l_r_d_c_r_B CR ON CR.Rate_Deck_Details_ID = DT.ID
LEFT OUTER JOIN tb_l_c CNT ON CNT.Code = CR.Country_Code
WHERE RD.Customer_ID = 'B758CC13-D323-43DE-A5CA-AD1E8FCFEF1C'
AND RD.ID= '29C23FC8-5C67-403F-9986-F5E279B2554E'
AND DT.VERSION = '1'
AND CR.Call_type_ID = 'INTERNATIONAL'
AND RDF.Call_type_ID = 'INTERNATIONAL'
--and CR.Country_Code = '225' and cr.Dialed_Digit = '02'
--ORDER BY CR.Country_Code, CR.Dialed_Digit
group by CR.Country_Code,CR.Dialed_Digit
having COUNT(*)>1

Thnks,
Harish Patil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-25 : 05:06:26

[code]
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY CR.Country_Code,CR.Dialed_Digit ORDER BY NEWID()) AS Rn,
CR.Country_Code As CountryCode,
CR.Dialed_Digit As AreaCode,
COUNT(*)
FROM tb_l_r_d RD
INNER JOIN tb_l_r_d_d DT ON DT.Rate_Deck_ID = RD.ID
INNER JOIN tb_l_r_d_f RDF ON RDF.Rate_Deck_Details_ID = DT.ID
INNER JOIN tb_l_r_d_c_r_B CR ON CR.Rate_Deck_Details_ID = DT.ID
LEFT OUTER JOIN tb_l_c CNT ON CNT.Code = CR.Country_Code
WHERE RD.Customer_ID = 'B758CC13-D323-43DE-A5CA-AD1E8FCFEF1C'
AND RD.ID= '29C23FC8-5C67-403F-9986-F5E279B2554E'
AND DT.VERSION = '1'
AND CR.Call_type_ID = 'INTERNATIONAL'
AND RDF.Call_type_ID = 'INTERNATIONAL'
--and CR.Country_Code = '225' and cr.Dialed_Digit = '02'
--ORDER BY CR.Country_Code, CR.Dialed_Digit
)t
WHERE Rn>1
[/code]
above will list duplicates. Check if they're once you want to clear and replace SELECT with DELETE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-10-25 : 05:24:03

Hello Friend,

With this modified sql statement from you, It will delete all duplicate records OR single record from the table.
I want to delete single record from the all duplicate record.

Thanks,
Harish Patil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-25 : 05:26:07

quote:
Originally posted by hspatil31

Hello Friend,

With this modified sql statement from you, It will delete all duplicate records OR single record from the table.
I want to delete single record from the all duplicate record.

Thanks,
Harish Patil

ok. which single record? any random one from duplicate set or any one particular like first/last etc?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-10-25 : 05:28:08

Any random one from duplicate set.

Thnks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-25 : 05:32:15

[code]SELECT *
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY CR.Country_Code,CR.Dialed_Digit ORDER BY NEWID()) AS Rn,
CR.Country_Code As CountryCode,
CR.Dialed_Digit As AreaCode,
COUNT(*)
FROM tb_l_r_d RD
INNER JOIN tb_l_r_d_d DT ON DT.Rate_Deck_ID = RD.ID
INNER JOIN tb_l_r_d_f RDF ON RDF.Rate_Deck_Details_ID = DT.ID
INNER JOIN tb_l_r_d_c_r_B CR ON CR.Rate_Deck_Details_ID = DT.ID
LEFT OUTER JOIN tb_l_c CNT ON CNT.Code = CR.Country_Code
WHERE RD.Customer_ID = 'B758CC13-D323-43DE-A5CA-AD1E8FCFEF1C'
AND RD.ID= '29C23FC8-5C67-403F-9986-F5E279B2554E'
AND DT.VERSION = '1'
AND CR.Call_type_ID = 'INTERNATIONAL'
AND RDF.Call_type_ID = 'INTERNATIONAL'
--and CR.Country_Code = '225' and cr.Dialed_Digit = '02'
--ORDER BY CR.Country_Code, CR.Dialed_Digit
)t
WHERE Rn=1

[/code]

this will select one out of group

check result and if its fine change it to delete

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources