- 论坛徽章:
- 0
|
表T结构如下:
COL1
COL2
COL3
中有重复记录(COL1,COL2为主键),如何删除
在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)
本文来自ChinaUnix博客,如果查看原文请点:http://blog.chinaunix.net/u/8659/showart_37709.html |
|