DECLARE @Record TABLE ( Id INT IDENTITY PRIMARY KEY, Fore...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

DECLARE @Record TABLE 
(
Id INT IDENTITY PRIMARY KEY,
ForeignId INT null,
IsDuplicateRecord bit NULL
)

INSERT INTO @Record (ForeignId)
VALUES (5), (5), (1), (2), (3),(1), (1), (NULL),(NULL),(NULL);

WITH CTE AS
(
SELECT *,
       ROW_NUMBER() OVER (PARTITION BY ForeignId ORDER BY Id) AS RN
FROM @Record
)
UPDATE CTE 
SET IsDuplicateRecord = 1
WHERE RN > 1

SELECT * 
FROM @Record

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results