CODEWITHSUNDEEP
sql-servert-sql
vincentsty
vincentsty

Reputation: 3221

buLK Update duplicate row value

Update duplicate column value with

Suppose I have a table with follow column ID, Code, IsDuplicate, Description

I have n records inside and I would like to bulk update the IsDuplicate value if there is duplicate code inside. Example

1 ABC null null
2 DEF null null
3 DEF null null
4 ABC null null
5 FGH null null

ID 1, 2, 3, 4 IsDuplicate will be updated to true.

How could it be done?

Upvotes: 0

Views: 111

Answers (2)

NewSQL
NewSQL

Reputation: 181

This will update all duplicate codes : 

UPDATE T
SET    ISDUPLICATE = 'TRUE'
FROM   YOURTABLE T
WHERE  EXISTS (SELECT 1
               FROM   (SELECT *
                       FROM   (SELECT ROW_NUMBER()
                                        OVER (
                                          PARTITION BY CODE
                                          ORDER BY ID)RN,
                                      *
                               FROM   YOURTABLE)A
                       WHERE  RN > 1)B
               WHERE  B.CODE = T.CODE)

Upvotes: 3

Jayesh Dhandha
Jayesh Dhandha

Reputation: 2119

You should use group by in select query.

SELECT code, COUNT(*) c FROM table GROUP BY code HAVING c > 1;

Then you can update it based on your requirements.

Upvotes: 0

Related Questions