SQL Server Performance

how delete duplicate rows from table without primary key

Discussion in 'SQL Server 2005 General Developer Questions' started by sasivashok, Nov 25, 2009.

  1. sasivashok New Member

    Hi,

    I have doubt. I am creating table and insert two rows.

    This is my table .

    BookName Price Author

    C++ 150 kanithkar
    C++ 150 kanithkar

    In the table no primary key. I need to delete duplicate rows without primary key and my output would be like

    BookName Price Author

    C++ 150 kanithkar

    Give a hintto me.Hope your's reply.

    Thanks
  2. arunyadav Member

    Hello Ashok,
    Kindly look at the below links.
    http://www.mssqltips.com/tip.asp?tip=1103
    http://www.sql-server-performance.com/articles/dba/delete_duplicates_p1.aspx
    http://sql-server-performance.com/Community/search/SearchResults.aspx?q=Delete+duplicate&a=0


  3. arunyadav Member

    create table #temp1 (BookName varchar(20) null, Price int null, Author varchar(20) null)

    insert into #temp1 (BookName,Price,Author)
    values ('C++', 150, 'kanithkar')
    insert into #temp1 (BookName,Price,Author)
    values ('C++', 150, 'kanithkar')
    insert into #temp1 (BookName,Price,Author)
    values ('C+', 150, 'kanithkar')
    insert into #temp1 (BookName,Price,Author)
    values ('C+', 150, 'kanithkar')
    insert into #temp1 (BookName,Price,Author)
    values ('C', 150, 'kanithkara')
    insert into #temp1 (BookName,Price,Author)
    values ('C', 150, 'kanithkara')

    select * from #temp1
    BookName Price Author
    -------------------- ----------- --------------------
    C++ 150 kanithkar
    C++ 150 kanithkar
    C+ 150 kanithkar
    C+ 150 kanithkar
    C 150 kanithkara
    C 150 kanithkara
    (6 row(s) affected)
    SET NOCOUNT ON
    declare @BookName varchar(20)
    Declare KURSOR cursor for select distinct BookName from #temp1
    OPEN KURSOR
    FETCH NEXT FROM KURSOR INTO @BookName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    Print @BookName
    DELETE TOP (SELECT COUNT(*) -1 FROM #temp1 WHERE BookName = @BookName)
    FROM #temp1
    WHERE BookName = @BookName
    FETCH NEXT FROM KURSOR INTO @BookName
    END
    CLOSE KURSOR
    DEALLOCATE KURSOR
    SET NOCOUNT OFF
    select * from #temp1
    BookName Price Author
    -------------------- ----------- --------------------
    C++ 150 kanithkar
    C+ 150 kanithkar
    C 150 kanithkara
    (3 row(s) affected)
  4. arunyadav Member

    Oops! forgot to drop #Temp1 [:D]
    select * from #temp1
    drop table #temp1

  5. sasivashok New Member

    Thanks dude. Great its working [:)]
  6. Madhivanan Moderator

    [quote user="arunyadav"]
    create table #temp1 (BookName varchar(20) null, Price int null, Author varchar(20) null)

    insert into #temp1 (BookName,Price,Author)
    values ('C++', 150, 'kanithkar')
    insert into #temp1 (BookName,Price,Author)
    values ('C++', 150, 'kanithkar')
    insert into #temp1 (BookName,Price,Author)
    values ('C+', 150, 'kanithkar')
    insert into #temp1 (BookName,Price,Author)
    values ('C+', 150, 'kanithkar')
    insert into #temp1 (BookName,Price,Author)
    values ('C', 150, 'kanithkara')
    insert into #temp1 (BookName,Price,Author)
    values ('C', 150, 'kanithkara')

    select * from #temp1
    BookName Price Author
    -------------------- ----------- --------------------
    C++ 150 kanithkar
    C++ 150 kanithkar
    C+ 150 kanithkar
    C+ 150 kanithkar
    C 150 kanithkara
    C 150 kanithkara
    (6 row(s) affected)
    SET NOCOUNT ON
    declare @BookName varchar(20)
    Declare KURSOR cursor for select distinct BookName from #temp1
    OPEN KURSOR
    FETCH NEXT FROM KURSOR INTO @BookName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    Print @BookName
    DELETE TOP (SELECT COUNT(*) -1 FROM #temp1 WHERE BookName = @BookName)
    FROM #temp1
    WHERE BookName = @BookName
    FETCH NEXT FROM KURSOR INTO @BookName
    END
    CLOSE KURSOR
    DEALLOCATE KURSOR
    SET NOCOUNT OFF
    select * from #temp1
    BookName Price Author
    -------------------- ----------- --------------------
    C++ 150 kanithkar
    C+ 150 kanithkar
    C 150 kanithkara
    (3 row(s) affected)
    [/quote]
    Note that this is posted in SQL Server 2005 forum. You should have given an example involving a row_number() than same old CURSOR method [;)]
  7. Adriaan New Member

    Madhi - not sure the row_number() solution will "always" be quicker. I have my head still stuck firmly in SQL 2000, and am still thinking of everything except row_number() and CTEs.
    Apart from that, the proper answer should include the comment that all tables need to have a unique key, and if there is no apparent candidate key that you can add an identity field as the row identifier.
  8. arunyadav Member

    :) Ya! will keep that in mind... [:)]
  9. rohit2900 Member

    Try this...
    Alter table mytable add temp_id int identity (1, 1) not null
    delete from mytable where temp_id not in(
    Select max(temp_id) from mytable
    group by c1, c2, ..., cn) --(Include all the columns which you want to filter for duplicates )
    Alter table mytable drop column temp_id
    Just cross check it for any syntax errors.....
  10. GeriReshef New Member

    Create Table #Tbl(BookName VarChar(50), Price Int, Author VarChar(50));
    Go
    Insert Into #Tbl Values ('C++', 150, 'kanithkar'), ('C++', 150, 'kanithkar');
    Go
    Select * From #Tbl;
    Go
    With T As (Select ROW_NUMBER() Over (Partition By BookName,Price,Author Order By BookName) Num From #Tbl)
    Delete From T Where Num>1;
    Go
    Select * From #Tbl; Go
  11. GeriReshef New Member

  12. a011010 New Member

    Hi,
    we can do it in many ways,
    Steps to remove duplicate tables:
    Suppose your table name is tblMain.
    -----------------------------------------------------------------------
    select distinct * into #tempTable from tblMain --Selecting distinct rows and store into Temp table
    Truncate table tblMain --Truncating MainTable
    insert into tblMain Select * from #tempTable --Inserting data into your maintable by selecting from Temp Table
    --------------------------------------------------------------------------
    here #tempTable is temporary table.
    Hope this will help you, if not working please reply me [:)]
  13. wayne.zjw New Member

    just delete all those rows and insert the only one i don't know hope it can help

Share This Page