Oracle WITH DELETE 按行号

发布于 2024-11-26 20:00:07 字数 446 浏览 0 评论 0原文

我正在编写一个按查看日期删除旧行的过程：

;WITH pv AS (
    SELECT
        RN = ROW_NUMBER() OVER (ORDER BY viewed DESC)
    FROM
        previouslyViewed
    WHERE
        userId = @userId
)
DELETE FROM pv WHERE RN >= 10

这适用于 SQL Server，但不适用于 Oracle。

Oracle 不支持WITH DELETE 组合。它也不支持 DELETE ORDER BY 组合（理论上可以与 rownum 一起使用以获得相同的结果）。我尝试使用 rownum 创建一个临时视图并从中删除，但出现 Oracle 错误 - 似乎在使用 rownum 时无法从视图中删除。

有人有任何指点吗？

原文

I am writing a procedure which removes old rows by their viewed date:

;WITH pv AS (
    SELECT
        RN = ROW_NUMBER() OVER (ORDER BY viewed DESC)
    FROM
        previouslyViewed
    WHERE
        userId = @userId
)
DELETE FROM pv WHERE RN >= 10

This works in SQL Server, but not in Oracle.

Oracle doesn't support the WITH DELETE combination. Nor does it support the DELETE ORDER BY combination (which could theoretically be used with rownum to achieve the same result). I have tried to create a temporary view with the rownum and delete from that, but I get an Oracle error - seemingly you cannot delete from a view when rownum is used.

Does anyone have any pointers?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

如若梦似彩虹 2024-12-03 20:00:07

您可以这样做：（

DELETE FROM previouslyViewed WHERE pkcol IN
( SELECT pkcol FROM
  (
      SELECT pkcol, ROW_NUMBER() OVER (ORDER BY viewed DESC) RN
      FROM previouslyViewed
      WHERE userId = :userId
  )
  WHERE RN >= 10
);

将 pkcol 更改为表的主键列）

You could do this instead:

DELETE FROM previouslyViewed WHERE pkcol IN
( SELECT pkcol FROM
  (
      SELECT pkcol, ROW_NUMBER() OVER (ORDER BY viewed DESC) RN
      FROM previouslyViewed
      WHERE userId = :userId
  )
  WHERE RN >= 10
);

(changing pkcol to the primary key column(s) of the table)

回复收藏 0 原文

~没有更多了~