Oracle:为什么我不能在delete子句中依赖ROWNUM [英] Oracle: Why I cannot rely on ROWNUM in a delete clause

问题描述

我有这样一条声明:

SELECT MIN(ROWNUM) FROM my_table
GROUP BY NAME 
HAVING COUNT(NAME) > 1);

此语句为我提供了第一个重复项的rownum，但是当将此语句转换为DELETE时，它将删除所有内容.为什么会这样呢?

This statement gives me the rownum of the first duplicate, but when transform this statement into DELETE it just delete everything. Why does it happen so?

推荐答案

这是因为ROWNUM是伪列，表示它们在物理上不存在.您最好使用rowid删除记录.

This is because ROWNUM is a pseudo column which implies that they do not exist physically. You can better use rowid to delete the records.

要删除重复项，您可以尝试如下操作:

To remove the duplicates you can try like this:

DELETE FROM mytable a
WHERE EXISTS( SELECT 1 FROM mytable b
              WHERE a.id = b.id
              AND a.name = b.name
              AND a.rowid > b.rowid )

这篇关于Oracle:为什么我不能在delete子句中依赖ROWNUM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！