Id use rownumber.

its so flexible and powerful for when you have duplicate records

try the following.

CREATE TABLE ##X

(COL1 NVARCHAR(10),COL2 NVARCHAR(10))

INSERT INTO ##X(COL1,COL2)

SELECT

'951' AS COL1,'454215' AS COL2

UNION ALL

SELECT '952','454215' UNION ALL

SELECT '953','454215' UNION ALL

SELECT '954','454215' UNION ALL

SELECT '955','454215' UNION ALL

SELECT '961','454216' UNION ALL

SELECT '962','454216' UNION ALL

SELECT '963','454217' UNION ALL

SELECT '964','454217' UNION ALL

SELECT '965','454217'

UPDATE A

SET COL2=COL2+'_D'

FROM (

SELECT *,ROW_NUMBER () OVER (PARTITION BY COL2 ORDER BY COL2) NUM FROM ##X) AS a

WHERE NUM>1

SELECT * FROM ##X