SQL Query On Duplicate Key 求教。

denthknyon

以下是我用来delete duplicated Key的query. 但是它delete的是original records.
1。要如何把original records 留着，只是把不要的删除?
2。可以把这个query run的更加快吗?



SET NOCOUNT ON
SET ROWCOUNT 1
WHILE 1 = 1
   BEGIN
    DELETE FROM trs_billdetails
    WHERE trsno IN (
        select trsno
            from trs_billdetails
                where trsno like ('V004%')
                group by trsno, billid,itemcode,plucode,basedqty,unitqty,unitprice
                having count(trsno) > 1)
    IF @@Rowcount = 0
         BREAK ;
    END
SET ROWCOUNT 0

winmxaa

DELETE FROM trs_billdetails a
WHERE rowid IN (
        select max(rowid)
            from trs_billdetails b
            where trsno like ('V004%')
             group by trsno
              having count(trsno) > 1)

mylover

DELETE t1 FROM trs_billdetails t1, trs_billdetails  t2 WHERE t1.id > t2.id AND t2.name LIKE ('V004%')

异军凸起

可以说明一下你的original record特征是什么吗？？