Need to Update based on ID and Date

I have the following SQL statement, which I think should update 1 field, using some pretty simple standard deviation logic, and based on ID and Date. I think the ID and Date has to be included to get everything aligned right. So, here is the code that I’m testing.

UPDATE Price_Test2
    SET Vol30Days = STDEV(PX_BID) OVER (ORDER BY ID_CUSIP, AsOfDate ROWS BETWEEN 30 PRECEDING AND CURRENT ROW) FROM Price_Test2
WHERE ID_CUSIP in (SELECT DISTINCT ID_CUSIP FROM Price_Test2)

It seems like it should work fine, but something is off because I’m getting an error that says: Cannot use both a From clause and a subquery in the where clause or in the data values list in an Update statement.

I am using SQL Server 2019.

Answer

You are using aggregation functions in an update. What you want is an updatable subquery (or CTE):

UPDATE p
    SET Vol30Days = new_Vol30Days,
        Vol60Days = new_Vol60Days,
        Vol90Days = new_Vol90Days
FROM (SELECT p.*,
             STDEV(PX_BID) OVER (ORDER BY Date ROWS BETWEEN 30 PRECEDING AND CURRENT ROW) as new_Vol30day,
             STDEV(PX_BID) OVER (ORDER BY Date ROWS BETWEEN 60 PRECEDING AND CURRENT ROW) as new_Vol60day,
             STDEV(PX_BID) OVER (ORDER BY Date ROWS BETWEEN 90 PRECEDING AND CURRENT ROW) as new_Vol60day
      FROM prices p
     ) p;