p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Classic ASP Databases (http://p2p.wrox.com/forumdisplay.php?f=62)
-   -   deleting only 1 duplicate entry (http://p2p.wrox.com/showthread.php?t=8838)

qwjunk February 1st, 2004 04:44 AM

deleting only 1 duplicate entry
 
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 .. ?


ankur_vachhani February 2nd, 2004 01:15 AM

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

qwjunk February 2nd, 2004 06:02 AM

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 ...

planoie February 2nd, 2004 11:45 AM

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.


All times are GMT -4. The time now is 05:32 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.