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.

CodePudding user response：

you can use window functions in an updatable CTE for this.

You haven't supplied any actual sample data so this is untested, however the following should work:

with x as (
  select Id, Occurence, count(*) over(partition by Id) qty
  from Table
)
update x 
set Occurence=Qty

CodePudding user response：

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

CodePudding user response：

You can use a CTE and calculate row number and update your table base on CTE

;WITH q
AS
(
SELECT Id,COUNT(1) 'RowNum'
FROM YourTable
GROUP BY Id
)
UPDATE YourTable
SET Occurrence=q.RowNum
FROM YourTable t
INNER JOIN q
ON t.Id=q.Id