ive two duplicate entries in my db .. now i want to delete only one of them preferably the second occurance only .. how do i do that using sql ?

when i use the normal sql ie. DELETE .. WHERE FileID=required ID

it deletes both occurences .. how do i fix this .. ?

use this sql query to delete only one duplicate record


    delete from your_table where rowid in
    ( select min(rowid) from your_table where FileID = your_requiredID)


        HTH

it deletes both the occurences .. im not sure if im applying the rowid part correctly ..

this is what is used..

delete from table where FILE_NO in ( select min(FILE_NO) from patient where FILE_NO = 89523)

i want the second occurence to be deleted ...

By "rowid" ankur was suggesting that you have something in your table that uniquely identifies each row (like an identity column). (Of course, if this were the case, then technically your wouldn't have duplicate rows.)

Is this a process you need to run regularly? Or is this something you are doing just to clean up some data? If this is a regular occurance, you should re-consider your table design. You shouldn't allow the insertion of duplicate data.

In order to only delete one row, you need some means of uniquely identifying each row so you have some criteria to separate duplicates. If you have that, then you could use the "min(rowid)" suggestion.

Peter
------------------------------------------------------
Work smarter, not harder.