Finding Occurrence of the duplicate values

I have table with 3 columns (id, Name, Occurrence), I want to update the Occurrence column ,based on the id column, attached snap for the reference.

for example if my id column has “606” value 3 times then my occurrent column should have 3 against all the “606” value.

Below is the method which I tried.

I tried to find the duplicate values using group by and Having clause and saved it in a temp table and from there I tried to join the table value from the temp table.

Answer

You can go for GROUP BY based approach also.

declare @TABLE TABLE(ID INT, NAME CHAR(3), occurance int null)

insert into @TABLE VALUES
(1,'AAA',NULL),(1,'AAA',NULL),(2,'CCC',NULL),(3,'DDD',NULL), (3,'DDD',NULL),(4,'EEE',NULL),(5,'FFF',NULL);

;WITH CTE_Table as
(
SELECT ID, COUNT(*) AS Occurance
FROM @table
group by id
)
UPDATE t
SET occurance = c.occurance
FROM @table t 
INNER JOIN CTE_Table as c
on C.ID = T.ID

SELECT * FROM @TABLE

ID	NAME	occurance
1	AAA	2
1	AAA	2
2	CCC	1
3	DDD	2
3	DDD	2
4	EEE	1
5	FFF	1