delete repeated rows

  • i have a table that broken. there are some rows repeated. How may i delete the repeated rows

    REFNOCOLUMN ........ ROWNOCOLUMN

    2356 1

    2356 2

    2356 2 ---must delete this row all the other columns are same with the upper row

    2356 3

    2356 4

  • DELETE D

    FROM

    (

    &nbsp&nbsp&nbsp&nbspSELECT

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspROW_NUMBER() OVER (PARTITION BY RefNoColumn, RowNoColumn ORDER BY RefNoColumn) AS RowID

    &nbsp&nbsp&nbsp&nbspFROM YourTable

    ) D

    WHERE RowID > 1

  • Simple way:

    Delete From YourTable T

    Where EXISTS( Select * from YourTable T2

    Where T.REFNOCOLUMN = T2.REFNOCOLUMN

    And T.ROWNOCOLUMN<T.ROWNOCOLUMN)

    This leaves the record with the highest ROWNOCOLUMN and deletes all of the duplicates below/before it.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • thank's for your helps but they did not work because of i think my server is sql 2000

  • Mine should work on SQL 2000. Did you try it?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • oopps wrong forum, sory again

  • yes i tried, but "select * from Invoicetable T " is incorrect syntax

  • Ok, my bad. Here is the corrected version for SQL 2000:

    Delete From YourTable

    Where EXISTS( Select * from YourTable T2

    Where YourTable.REFNOCOLUMN = T2.REFNOCOLUMN

    And YourTable.ROWNOCOLUMN<T.ROWNOCOLUMN)

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • thank you again, i think it's working i'll trie a few on it

  • Glad we could help.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • no it's working wrong,

    Delete From FATHAR

    Where EXISTS( Select * from FATHAR T2

    Where FATHAR.FATHARREFNO = T2.FATHARREFNO

    And FATHAR.FATHARSIRANO < T2.FATHARSIRANO)

    is deleting all rows without rownumber 1

    Delete From FATHAR

    Where EXISTS( Select * from FATHAR T2

    Where FATHAR.FATHARREFNO = T2.FATHARREFNO

    And FATHAR.FATHARSIRANO < T2.FATHARSIRANO)

    is deleting all rows without rownumber 6 and

    Delete From FATHAR

    Where EXISTS( Select * from FATHAR T2

    Where FATHAR.FATHARREFNO = T2.FATHARREFNO

    And FATHAR.FATHARSIRANO = T2.FATHARSIRANO)

    is deleting all

    TABLE FATHAR

    FATHARREFNO|FATHARSIRANO

    1 | 1

    1 |1

    1 |2

    1 |2

    1 |3

    1 |3

    1 |4

    1 |2

    1 |5

    1 |5

    1 |6

  • yaman bas (8/28/2008)


    no it's working wrong,

    Delete From FATHAR

    Where EXISTS( Select * from FATHAR T2

    Where FATHAR.FATHARREFNO = T2.FATHARREFNO

    And FATHAR.FATHARSIRANO < T2.FATHARSIRANO)

    is deleting all rows without rownumber 1

    Delete From FATHAR

    Where EXISTS( Select * from FATHAR T2

    Where FATHAR.FATHARREFNO = T2.FATHARREFNO

    And FATHAR.FATHARSIRANO < T2.FATHARSIRANO)

    is deleting all rows without rownumber 6 and

    I am sorry, but this makes no sense: both of these queries are the same, how can they be doing three different things?!? You must be doing something other than what you are telling us, because what you are describing is impossible.

    Delete From FATHAR

    Where EXISTS( Select * from FATHAR T2

    Where FATHAR.FATHARREFNO = T2.FATHARREFNO

    And FATHAR.FATHARSIRANO = T2.FATHARSIRANO)

    is deleting all

    Yes, of course it will. And this is not what I gave you.

    Please provide some table defintions and sample data so that we can test this ourselves. See this link for instructions on how to provide this information: http://www.sqlservercentral.com/articles/Best+Practices/61537/

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • OK, I think I see part of the problem: I misunderstood the role of the second column: you just want to eliminate all duplicates.

    Easy enough:

    Select Distinct *

    Into #TempFATHAR

    From FATHAR;

    Delete From FATHAR;

    Insert into FATHAR

    Select * from #TempFATHAR

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Thank you very much RBarry, you are great, i was just working about the give you a sample table.

    your query worked good on the sample table and i'll trie it on mine.

  • Even this works....

    SET ROWCOUNT 1

    DELETE YOURTABLE

    FROM YOURTABLE A WHERE (SELECT COUNT(*) FROM YOURTABLE B WHERE A.COLNAME=B.COLNAME) >1

    WHILE @@ROWCOUNT >0

    DELETE YOURTABLE

    FROM YOURTABLE A WHERE (SELECT COUNT(*) FROM YOURTABLE B WHERE A.COLNAME=B.COLNAME ) >1

    SET ROWCOUNT 0

    [font="Times New Roman"]For better assistance in answering your questions
    Click Here[/url][/font]

Viewing 15 posts - 1 through 15 (of 15 total)

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