Post reply

how delete duplicate rows from table without primary key

  • November 25, 2009 at 11:44 pm

    #135604

    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

  • November 26, 2009 at 12:10 am

    #1084464

    See if this helps you:

    http://blogs.techrepublic.com.com/datacenter/?p=420

  • November 26, 2009 at 9:42 am

    #1084646

    hi use the below query.

    WITH DUPE AS

    (

    SELECT BOOKNAME,PRICE,AUTHOR,ROW_NUMBER() OVER(ORDER BY BOOKNAME,PRICE,AUTHOR ) AS ROWNUMBER

    FROM TABLENMAE )

    DELETE FROM DUPE

    WHERE ROWNUMBER NOT IN (SELECT MAX(ROWNUMBER) FROM DUPE GROUP BY BOOKNMAE,PICE,AUTHOR

    )

    Regards,

    MC

    Thanks & Regards,
    MC

  • November 26, 2009 at 10:51 am

    #1084666

    ashok

    CREATE TABLE #Books(BookName VARCHAR(20), Price MONEY, Author VARCHAR(50))

    INSERT INTO #Books

    SELECT 'C++', 150, 'kanithkar' UNION ALL

    SELECT 'C++', 150, 'kanithkar' UNION ALL

    SELECT 'C#', 150, 'kanithkar' UNION ALL

    SELECT 'Net', 250, 'smith' UNION ALL

    SELECT 'Net', 250, 'jones' UNION ALL

    SELECT 'SQL', 250, 'jack'

    ;with numbered as(SELECT rowno=row_number() over

    (partition by Bookname, Author order by Bookname),Bookname,Author,Price from #Books)

    --For testing use:

    SELECT * FROM numbered --To check if output is correct

    --Once tested and output is correct replace above statement with

    DELETE from numbered WHERE rowno > 1

    My select output:

    rownoBookname AuthorPrice

    1C# kanithkar150.00

    1C++ kanithkar150.00

    2C++ kanithkar150.00

    1Net jones 250.00

    1Net smith 250.00

    1SQL jack 250.00

    Changed T-SQL after testing to use DELETE. Resuls:

    (1 row(s) affected)

    BookName Price Author

    C++ 150.00kanithkar

    C# 150.00kanithkar

    Net 250.00smith

    Net 250.00jones

    SQL 250.00jack

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • November 27, 2009 at 9:02 am

    #1084943

    bitbucket has the better solution.

    also, may I recommend you add a primary key to your table(s)?? 🙂

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • November 27, 2009 at 11:16 am

    #1084993

    TheSQLGuru

    Thank you for the rcommendation.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply