CODEWITHSUNDEEP
sql-server
Liong Loong
Liong Loong

Reputation: 45

SQL Server - Update whole row to null if duplicate

I have this table in SQL Server 2008

+----------+----------+----------+
|    ID    |   Date   |   Desc   |
+----------+----------+----------+
|   I.001  |01/01/2014|   Test   |
|   I.001  |01/01/2014|   Test2  |
|   I.002  |02/01/2014|   Test3  |
|   I.002  |02/01/2014|   Test4  |
|   I.002  |02/01/2014|   Test5  |
+----------+----------+--------- +

and the table goes on with similar pattern. I am expecting a result like this

+----------+----------+----------+
|    ID    |   Date   |   Desc   |
+----------+----------+----------+
|   I.001  |01/01/2014|   Test   |
|   I.001  |   NULL   |   NULL   |
|   I.002  |02/01/2014|   Test3  |
|   I.002  |   NULL   |   NULL   |
|   I.002  |   NULL   |   NULL   |
+----------+----------+--------- +

Is there any SQL script allowing me to get the above result? Many thanks in advance.

Upvotes: 0

Views: 130

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Duplicate here means having the same ID and Date. The row to be retained will be the one with first DESC alphabetically.

WITH Cte AS(
    SELECT *,
        RN = ROW_NUMBER() OVER(PARTITION BY ID, [Date] ORDER BY [Desc])
    FROM YourTable
)
UPDATE Cte
    SET [Date] = NULL,
        [Desc] = NULL
WHERE RN > 1

Upvotes: 4

Related Questions