CODEWITHSUNDEEP
sqlwindow-functionssql-server-2014
SASUSMC
SASUSMC

Reputation: 681

Windowed Functions in SQL Server 2014

Does anyone know if you can use window functions in a set statement in SQL Server 2014?

I am trying to set a column in an existing table to a count of results from another table. I can do this with temp tables, just thought it would be cleaner with a windowing function.

Update #Totals
  Set 
    TotalContacts = Count(C.PatientID) Over (Partition By C.HospCode)
  From
  #Totals as T
  Inner Join
  #Contacts as C
  On
  T.HospCode = C.HospCode

Upvotes: 1

Views: 607

Answers (1)

Martin Smith
Martin Smith

Reputation: 454000

No.

SQL Server 2014 doesn't allow windowed functions to be used directly in the set. This would not be standard SQL.

Since 2005 you have been able to do this though.

WITH CTE 
     AS (SELECT TotalContacts, 
                Count(C.PatientID) 
                  OVER (Partition BY C.HospCode) AS Cnt
         FROM   Totals AS T 
                INNER JOIN Contacts AS C 
                        ON T.HospCode = C.HospCode) 
UPDATE CTE 
SET    TotalContacts = Cnt

Upvotes: 1

Related Questions