sql - 假设表示例中有 5 个重复行，如下所示，我想将除一行之外的所有行都更新为 NULL - sql 查询

Question

Location | Entity_id
UK       | 1320
UK       | 1320
UK       | 1320
UK       | 1320
UK       | 1320

我只想保留第一行 entity_ID 并将所有其他行保留为 NULL.SQL QUERY 来做到这一点？

标签： sql

Answer 1

;WITH CTE AS
(
 SELECT Id, 
       Location, 
       Entity_id, 
       ROW_NUMBER() OVER(PARTITION BY Entity_id ORDER BY Id ASC) rn
FROM Table
WHERE Entity_id = 1320
)

UPDATE CTE
SET Entity_id = NULL
WHERE rn > 1

假设您有一个主键作为 Id