Update column as Duplicate

Question

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.

As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, Oct 23 '21 at 09:14
Maybe this is useful: https://stackoverflow.com/questions/20539095/update-top-1-record-in-table-sql-server — Carlos, Oct 23 '21 at 09:57
See [Correlated subquery](https://en.wikipedia.org/wiki/Correlated_subquery). — HABO, Oct 23 '21 at 13:07

score 1 · Accepted Answer · answered Oct 23 '21 at 10:29

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)

score 1 · Answer 2 · answered Oct 23 '21 at 18:55

1

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;

answered Oct 23 '21 at 18:55

Charlieface

52,284
6
19
43

Update column as Duplicate

2 Answers2