locked
How To Delete Only One Of The Duplicates RRS feed

  • Question

  • SELECT *FROM pipe_Heat_mast _T WHERE (SELECT COUNT(*)FROM pipe_Heat_mast Where PIPE_SIZE=_T.PIPE_SIZE and HEAT_CODE= _T.HEAT_CODE)

    1.As i Execute Above statement, i am getting below result of duplicates,

    PIPE_SIZE HEAT_CODE HEATNUMBER MILL_NAME
    3 1/2"  E1989935 E10809945 Tianhe
    
    3 1/2"  E1989935 E10809945 Tianhe
    3 1/2"  Y63  95D2463  Hubei
    What i need is i have to delete only One record with a combination of PIPE_SIZE,HEAT_CODE
    I tried below code but it is deleteing all the records,

    Alluru.Midhun Kumar Singh





    • Moved by Tom Phillips Tuesday, May 24, 2011 1:50 PM TSQL question (From:SQL Server Database Engine)
    • Edited by Midhunkumarsingh Tuesday, December 25, 2012 7:12 AM
    Tuesday, May 24, 2011 7:56 AM

Answers

All replies

  • http://dimantdatabasesolutions.blogspot.com/2007/02/dealing-with-duplicates.html
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, May 24, 2011 8:02 AM
    Answerer
  • Hi Allur,

    Here is what i think you need to do.

    create table pipe_Heat_mast 
    (pipe_size varchar(50),
    heat_code varchar(50),
    heatnumber varchar(50),
    mill_name varchar(50))
    go
    
    insert into pipe_heat_mast values('3 1/2"','E1989935','E10809945','Tianhe')
    insert into pipe_heat_mast values('3 1/2"','E1989935','E10809945', 'Tianhe')
    insert into pipe_heat_mast values('3 1/2"','E1989936','D10809945', 'Tian')
    insert into pipe_heat_mast values('3 1/2"','E1989936','D10809945', 'Tian')
    insert into pipe_heat_mast values('3 1/2"','Y63', '95D2463', 'Hubei')
    insert into pipe_heat_mast values('3 1/2"','Y63', '95D2463', 'Xinyegang')
    insert into pipe_heat_mast values('3 1/2"','Y64', '95D2453', 'Hubeisee')
    insert into pipe_heat_mast values('3 1/2"','Y64', '95D2453', 'Xinyegangeee')
    
    go
    
    SELECT * FROM pipe_Heat_mast	;			 
    WITH Duplicates
    AS
    (
    	SELECT *,ROW_NUMBER() OVER(PARTITION BY PIPE_SIZE,HEAT_CODE order by PIPE_SIZE,HEAT_CODE) as cnt FROM pipe_Heat_mast _T WHERE 
    				(SELECT COUNT(*)FROM pipe_Heat_mast Where PIPE_SIZE=_T.PIPE_SIZE and HEAT_CODE= _T.HEAT_CODE)>1
    				 
    )
    DELETE FROM Duplicates WHERE cnt> 1; 	 
    SELECT * FROM pipe_Heat_mast				 
    				 
    		
    

    Regards Gursethi Blog: http://sqlfundas.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++
    Tuesday, May 24, 2011 9:40 AM
  • Declare @t table
    (pipe_size varchar(50),
    heat_code varchar(50),
    heatnumber varchar(50),
    mill_name varchar(50))
    
    
    insert into @t values('3 1/2"','E1989935','E10809945','Tianhe')
    insert into @t values('3 1/2"','E1989935','E10809945', 'Tianhe')
    insert into @t values('3 1/2"','E1989935','E10809945', 'Tianhe')
    insert into @t values('3 1/2"','E1989936','D10809945', 'Tian')
    insert into @t values('3 1/2"','E1989936','D10809945', 'Tian')
    insert into @t values('3 1/2"','Y63', '95D2463', 'Hubei')
    insert into @t values('3 1/2"','Y63', '95D2463', 'Xinyegang')
    insert into @t values('3 1/2"','Y64', '95D2453', 'Hubeisee')
    insert into @t values('3 1/2"','Y64', '95D2453', 'Xinyegangeee')
    
    
    ;			 
    WITH Duplicates
    AS
    (
    	SELECT *,Row_number() OVER(PARTITION BY PIPE_SIZE,HEAT_CODE,heatnumber,mill_Name order by PIPE_SIZE,HEAT_CODE,heatnumber,mill_Name) as cnt FROM @t A Where
    (SELECT COUNT(*)FROM @t X Where X.PIPE_SIZE=A.PIPE_SIZE and X.HEAT_CODE= A.HEAT_CODE and X.heatnumber=A.Heatnumber and X.mill_name=A.mill_Name)>1
    
    )
    
    Delete from duplicates Where cnt=1 --Delete only single entry from multiple duplicates data
    
    --Delete from duplicates Where cnt > 1 --- Delete all duplicates except 1
    

    If this answer is helpful to you .. Please mark as Answer....
    Tuesday, May 24, 2011 10:18 AM
  • Hello,

    Please use below query

    DELETE
    FROM
    MyTable
    WHERE ID NOT IN
    (
    SELECT MAX(ID)
    FROM MyTable
    GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

     

    or you can check below link for verification

    http://www.sql-server-performance.com/2003/delete-duplicates/

     

    Thanks,


    Raheel Khan
    Tuesday, May 24, 2011 11:09 AM
    1.  

    INSERT INTO @pipe_Heat_mast VALUES('3 1/2"','E10809935')
    INSERT INTO @pipe_Heat_mast VALUES('3 1/2"','Y63')
    INSERT INTO @pipe_Heat_mast VALUES('5"','95D2463')
    INSERT INTO @pipe_Heat_mast VALUES('5"','98DA1848')
    INSERT INTO @pipe_Heat_mast VALUES('5"','D71')
    INSERT INTO @pipe_Heat_mast VALUES('5"','T9536')
    INSERT INTO @pipe_Heat_mast VALUES('5"','VA42')
    INSERT INTO @pipe_Heat_mast VALUES('5"','Y8165')
    INSERT INTO @pipe_Heat_mast VALUES('5"','Z27')
    INSERT INTO @pipe_Heat_mast VALUES('5"','Z29')
    INSERT INTO @pipe_Heat_mast VALUES('5"','Z30')
    INSERT INTO @pipe_Heat_mast VALUES('8"','DA7')

    All the above values are repeating twice,What i need is i have to delete one row & have to keep only one in database.


    Allur.Midhunkumarsingh

    This query will work well for duplicate data ... I dont see any wrong with query... Can you please tell about what duplicates are not getting deleted with this query. ?

    ;
    WITH DUPLICATES
    AS
    (
    SELECT *,Row_number() OVER(PARTITION BY PIPE_SIZE,HEAT_CODE ORDER BY PIPE_SIZE,HEAT_CODE) as CNT FROM @pipe_Heat_mast A WHERE
    (SELECT COUNT(*)FROM @pipe_Heat_mast X Where X.PIPE_SIZE=A.PIPE_SIZE and X.HEAT_CODE= A.HEAT_CODE)>1

    )

    Delete from DUPLICATES Where CNT>1

    ---------------------------

     


    If this answer is helpful to you .. Please mark as Answer....
    Wednesday, May 25, 2011 1:47 PM
  • Please use below query to remove the duplicate entries from your database tables

     

       ;
          WITH MultipleRecords
          AS
          (
              SELECT *,Row_number() OVER(PARTITION BY PIPE_SIZE,HEAT_CODE ORDER BY PIPE_SIZE,HEAT_CODE) as Num
            FROM pipe_Heat_mast _T  A WHERE
          (SELECT COUNT(*)FROM pipe_Heat_mast _T X Where X.PIPE_SIZE=A.PIPE_SIZE and X.HEAT_CODE= A.HEAT_CODE)>1

          )
        Delete from MultipleRecords Where Num>1

     

    Thanks,


    Raheel Khan
    Wednesday, May 25, 2011 2:25 PM
  • Thursday, May 26, 2011 5:56 AM