Update column as Duplicate-CodePudding

I have a table with three columns, A, B, and status.

first, I filter the table to get only duplicate value

using this query

    SELECT  A
   FROM Table_1
     GROUP BY  A
    HAVING COUNT(A) >1

the output :

In the second step, I need to check if column B has a duplicate value or not, if have duplicate I need to update the status as D.

I try this query

UPDATE Table_1

 SET status = 'D'

 WHERE exists

 (SELECT  B
  FROM Table_1
 GROUP BY  B
HAVING COUNT(B) >1)

but it is updated all the rows.

CodePudding user response：

You can do this with an updatable CTE without any further joins by using a windowed COUNT

WITH d AS (
    SELECT *,
      cnt = COUNT(*) OVER (PARTITION BY a, b)
    FROM t
)
UPDATE d
SET status = 'D'
WHERE cnt > 1;

CodePudding user response：

The following does what you need using row_number to identify any group with a duplicate and an updateable CTE to check for any row that's part of a group with a duplicate:

with d as (
    select *, row_number() over(partition by a,b order by a,b) dn
    from t
)
update d set d.status='D'
where exists (select * from d d2 where d2.a=d.a and d2.b=d.b and d2.dn>1)