Search code examples
sqlsql-server-2017

How to a Update Column with a Count of another Column in the same table

This select statement works fine and gives me the answer I need as I'm trying to see how many times an ID appears in the same table as it will tell me how many locations that ID shows up in (locations as in States).

SELECT ID, COUNT(ID) as CountOfID
FROM Locations_Number
GROUP BY ID
ORDER BY CountOfID Asc;

However how can I translate this into an UPDATE statement so I can refer to it The below obviously doesn't work because of the "CountOfLocations" (Invalid column name 'CountOfLocations'). Could I use an inner join here ?

UPDATE Locations_Number
set CountOfID =
(SELECT COUNT(ID) as CountOfID
FROM Locations_Number
GROUP BY ID, CountOfID)
Solution

  • Use a window function and updatable CTE:

    with toupdate as (
      select ln.*, count(*) over (partition by id) as cnt
      from Locations_Number ln
     )
update toupdate
    set CountOfID = cnt;