title: Update all the following values in column depending on previous value
body: <p>I am looking to do a &quot;cascading&quot; update on a column with all null values except the &quot;first&quot;. The order is determined by date ascending.
  I have this table</p>
  <pre><code>SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  CREATE TABLE [dbo].[StockA]
  (
      [date] [date] NOT NULL,
      [PercentChange] [decimal](19, 6) NULL,
      [Price] [decimal](16, 2) NULL
  ) ON [PRIMARY]
  GO
  
  INSERT INTO [dbo].[StockA] ([date], [PercentChange], [Price]) 
  VALUES (CAST(N'2021-08-19' AS Date), CAST(-0.005100 AS Decimal(19, 6)), NULL)
  
  INSERT INTO [dbo].[StockA] ([date], [PercentChange], [Price]) 
  VALUES (CAST(N'2021-08-20' AS Date), CAST(0.013000 AS Decimal(19, 6)), NULL)
  
  INSERT INTO [dbo].[StockA] ([date], [PercentChange], [Price]) 
  VALUES (CAST(N'2021-08-23' AS Date), CAST(0.015400 AS Decimal(19, 6)), NULL)
  
  INSERT INTO [dbo].[StockA] ([date], [PercentChange], [Price]) 
  VALUES (CAST(N'2021-08-24' AS Date), CAST(0.009500 AS Decimal(19, 6)), NULL)
  
  INSERT INTO [dbo].[StockA] ([date], [PercentChange], [Price]) 
  VALUES (CAST(N'2021-08-18' AS Date), CAST(0.010000 AS Decimal(19, 6)), CAST(100.00 AS Decimal(16, 2)))
  GO
  
  SELECT * 
  FROM StockA 
  ORDER BY date ASC
  </code></pre>
  <p><a href="https://i.stack.imgur.com/f2cuv.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/f2cuv.png" alt="Pic1" /></a></p>
  <p>The four NULL values should be calculated based on the previous, starting with first NULL value on date 2021-08-19 gives is a calculation of 100(this is the previous value) * (1+PercentChange) = 99.49</p>
  <p>I have tried this</p>
  <pre><code>WITH CTE AS
  (
      SELECT 
          date, PercentChange, Price,
          (LAG(Price) OVER (Order by date))*(1+PercentChange) AS NextPrice
      FROM
          StockA
  )
  UPDATE CTE
  SET Price = NextPrice;
  </code></pre>
  <p>which incorrectly gives me this:</p>
  <p><a href="https://i.stack.imgur.com/B7OB5.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/B7OB5.png" alt="Pic2" /></a></p>
  <p>The calculation is correct. The row 2021-08-19 should be 99.49. Nothing wrong there. But something is wrong with my update statement apparently. If I run my CTE query again, the correct value is added to the next row and disappearing from the previous.</p>
  <p>Does anybody know how to fill the entire &quot;Price&quot; column, so that there are no NULLS, with calculated values that are based on the &quot;first&quot; (ordered by date ascending) Price?</p>
  <p>I am using SQL Server 2019.</p>