Delete rows where certain value occurs more than once SQL

2018-06-22 02:44:45

Possible Duplicate:
Removing duplicate rows from table in Oracle

Here's an example of what I currently have:

ID        Name1    Name2
ABC123    Bob      Jones
ABC123    Bob      Smith
ABC123    Bob      Hammond
DEF234    Tim      Whatever

I'd like the table above to look like this:

ID        Name1    Name2
ABC123    Bob      Jones
DEF234    Tim      Whatever

I'd like to delete all rows where the ID field is shared, I don't care which ones are kept or deleted, just that the number of total rows is equal to the number of unique IDs.

DELETE FROM YOURTABLE Y WHERE ROWID > (SELECT min(rowid) FROM YOURTABLE X
WHERE X.ID = Y.ID)

如果您因为任何原因没有全球唯一ID，那么应该这样工作：

DELETE FROM Table WHERE CONCAT(ID,Name1,Name2) NOT IN ( 
       SELECT MIN(CONCAT(ID,Name1,Name2)) FROM Table GROUP BY ID)

 DELETE FROM your_table
      WHERE ROWID IN (
               SELECT rid
                  FROM (SELECT ROWID rid,
                           ROW_NUMBER () OVER (PARTITION BY ID ORDER BY ROWID) rn
                         FROM your_table)
                WHERE rn <> 1);

链接地址: http://www.djcxy.com/p/62066.html

上一篇: orderby / where查询SQL中的哪种索引？

下一篇: 删除某些值出现多次SQL的行