CSDN博客

img q30

在Oracle中如何删除重复的记录?

发表于2004/9/15 11:30:00  901人阅读

分类: Oracle

表T结构如下

  COL1
  COL2
  COL3

中有重复记录(COL1,COL2为主键),如何删除

1、有少数重复记录(在col1,col2上有索引比较好)

DELETE T
   WHERE (COL1,COL2) IN
  (SELECT COL1,COL2 FROM T GROUP BY COL1,COL2 HAVING COUNT(*) > 1)
     AND
  ROWID NOT IN
         (SELECT MIN(ROWID) FROM T GROUP BY COL1,COL2 HAVING COUNT(*) > 1)

2、大部份记录有重复记录

DELETE T WHERE ROWID NOT IN
   (SELECT MIN(ROWID) FROM T GROUP BY COL1,COL2)

3、其他写法

DELETE T WHERE ROWID IN
     (SELECT A.ROWID FROM T A,T B
      WHERE A.COL1=B.COL1 AND A.COL2 = B.COL2 AND A.ROWID > B.ROWID)

阅读全文
0 0

相关文章推荐

img
取 消
img