SQL Server - 如果重复，则将整行更新为null

I have this table in SQL Server 2008

我在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.

是否有任何SQL脚本允许我获得上述结果？提前谢谢了。

1 个解决方案

#1

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

这里重复意味着具有相同的ID和日期。要保留的行将是按字母顺序排在第一个DESC的行。

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