在PostgreSQL中的分区上更新DELETED_AT列

发布于 2025-02-09 22:24:08 字数 343 浏览 2 评论 0原文

快速问题，我只有在表中有重复（分区列＆gt; 1）并根据分区概念选择它时才尝试更新列，但是当前查询会更新整个表！请检查下面的查询，任何线索都将不胜感激:)

UPDATE public.database_tag
SET deleted_at= '2022-04-25 19:33:29.087133+00'
FROM (
SELECT *,
row_number() over (partition by title order by created_at) as RN
FROM public.database_tag
ORDER BY RN DESC) X 
WHERE X.RN > 1

非常感谢！

原文

Quick question, I'm trying to update a column only when there are duplicates(partition column > 1) in the table and have selected it based on partition concept, But the current query updates the whole table! please check the query below, Any leads would be greatly appreciated :)

UPDATE public.database_tag
SET deleted_at= '2022-04-25 19:33:29.087133+00'
FROM (
SELECT *,
row_number() over (partition by title order by created_at) as RN
FROM public.database_tag
ORDER BY RN DESC) X 
WHERE X.RN > 1

Thanks very much!

分享到QQ

分享到微博

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

发布评论

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

本宫微胖 2025-02-16 22:24:08

假设每个行都有唯一的ID，则可以像以下那样完成。

UPDATE database_tag 
SET deleted_at= '2022-04-25 19:33:29.087133+00'
WHERE <some_unique_id> in (
  select <some_unique_id> from (
     SELECT <some_unique_id>,
       row_number() over (partition by title order by created_at) as RN
     FROM public.database_tag
  ) X 
   WHERE X.RN > 1
)

或者我们可以反向查询以更新所有ID之外的所有内容

UPDATE database_tag
SET deleted_at= '2022-04-25 19:33:29.087133+00'
WHERE <some_unique_id> not in (
  select distinct on (title)
     <some_unique_id> from database_tag
  order by title, created_at
  )

Assuming that every row have unique ID it can be done like below.

UPDATE database_tag 
SET deleted_at= '2022-04-25 19:33:29.087133+00'
WHERE <some_unique_id> in (
  select <some_unique_id> from (
     SELECT <some_unique_id>,
       row_number() over (partition by title order by created_at) as RN
     FROM public.database_tag
  ) X 
   WHERE X.RN > 1
)

Or we can reverse query to update all but set of ID's

UPDATE database_tag
SET deleted_at= '2022-04-25 19:33:29.087133+00'
WHERE <some_unique_id> not in (
  select distinct on (title)
     <some_unique_id> from database_tag
  order by title, created_at
  )

回复收藏 0 原文

~没有更多了~