Oracle根据条件删除重复项 [英] Oracle deleting duplicates based on a condition

问题描述

我试图从表中删除一些重复项，并尝试保留ID1列的重复项之一.

I am trying to delete some duplicates out of a table and trying to retain one of the duplicates for ID1 column.

使用此查询，我设法根据ROWID进行删除.

Using this query I managed to delete based on ROWID.

delete from    tabela.lorik
where ROWID not in (
select MAX(ROWID) 
from     tabela.lorik
GROUP BY ID1) 

现在，我要删除在NETAMT = 0时发现重复的所有那些记录

Now I want to delete all those records found duplicate where NETAMT = 0

推荐答案

您可以使用以下查询来实现:

You can achieve this using the following query:

delete from  tabela.lorik O
where O.netamt = 0 
AND EXISTS (SELECT 1 FROM tabela.lorik I
WHERE I.ID = O.ID AND I.netamt <> 0)

我假设您只需要删除netamount = 0处的记录，否则请在下面注释.

I am assuming that you need to delete only records where it is netamount = 0, If not then do comment below.

如果您要保留一个非零条目并删除所有其他条目(对于所有零，将保留一个以零表示的条目netamount)，则可以使用以下查询:

If you are looking to retain one non-zero entry and delete all others(In case of all the zeros, one entry with zero as netamount will be retained) then you can use the following query:

DELETE FROM TABELA.LORIK O
WHERE
    ROWID IN (
        SELECT
            RWID
        FROM
            (
                SELECT
                    ROWID AS RWID,
                    ROW_NUMBER() OVER(
                        PARTITION BY ID
                        ORDER BY
                            CASE
                                WHEN NETAMT = 0 THEN 2
                                ELSE 1
                            END
                    ) AS RN
                FROM
                    TABELA.LORIK
            )
        WHERE
            RN > 1
    );

干杯！

这篇关于Oracle根据条件删除重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！