如何删除oracle中的重复项?

vsmadaxz  于 2021-07-29  发布在  Java
关注(0)|答案(4)|浏览(373)

我试图删除表中的重复行,但它给了我一个错误

SQL Error: ORA-01732: data manipulation operation not legal on this view
01732. 00000 -  "data manipulation operation not legal on this view"

* Cause:
* Action:

以下是我的疑问:

delete from
(
SELECT A.*,
row_number() over (partition by x,y order by y) as dup
FROM table A
WHERE x='13' AND EXTRACT(YEAR FROM y)='2020'
) A
where dup>1
nbysray5

nbysray51#

如果 y 是唯一的,则通常使用:

delete from a
    where x = '13' and
          y >= date '2020-01-01' and
          y < date '2021-01-01' and
          rowid > (select min(a2.y)
                   from a a2
                   where a2.x = a.x and a2.y >=  date '2020-01-01' and y < date '2021-01-01'
                  );

你也可以使用 rowid --哪怕 y 不是唯一的:

delete from a
    where x = '13' and
          y >= date '2020-01-01' and
          y < date '2021-01-01' and
          y <> (select min(rowid) keep (dense_rank first order by y)
               from a a2
               where a2.x = a.x and a2.y >=  date '2020-01-01' and y < date '2021-01-01'
              );
tquggr8v

tquggr8v2#

你可以选择 rowid 要删除的,然后使用 in 删除条件:

DELETE FROM tablea
WHERE  rowid IN (SELECT rowid
                 FROM   (SELECT rowid, ROW_NUMBER() OVER (PARTITION BY x, y ORDER BY y) AS dup

                         FORM   tablea
                         WHERE  x = '13' AND EXTRACT(YEAR FROM y) = '2020')
                 WHERE  dup > 1)
eqzww0vc

eqzww0vc3#

一个简单的技巧是使用 EXISTS 具体如下:

DELETE FROM TABLE_A A
WHERE A.X = '13' 
  AND A.Y >= TRUNC(SYSDATE,'YY')  -- 01-01-2020
  AND A.Y < TRUNC(ADD_MONTHS(SYSDATE,12),'YY') -- 01-01-2021
  AND EXISTS (SELECT 1 
                FROM TABLE_A AA
               WHERE AA.X = A.X
                 AND AA.Y = A.Y
                 AND AA.ROWID > A.ROWID);
mqxuamgl

mqxuamgl4#

另一个标准模式是:

delete sometable
where  rowid in (
       ( select lead(rowid) over (partition by y order by z) as dup
         from   sometable a
         where  x = '13'
         and    ... )

哪里 y 是一个或多个重复的键(例如。 customer_id )以及 z 排序键允许您选择要保留的“第一行”或“最后一行”(例如。 created_date ,如果您在多天内创建了同一个客户,并且希望保留第一个客户并删除其余客户)。
你的例子中的这一部分似乎没有意义:

(partition by x,y order by y)

因为每个分区中的所有内容都已经具有相同的 x 以及 y . 您可能希望按不属于分区键的内容排序。

相关问题