Update a deleted_at column on partition in PostgreSQL-CodePudding

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!

CodePudding user response：

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
  )