Update a field by get value from two-line On one table

Question

How can I update a column by getting a value from two-line on one table?

I test this

update [Exams_GAT_SAT] 
set [SAT_improve] =(select ((select SAT_av from Exams_GAT_SAT where ayear = 1443)
-(select SAT_av from Exams_GAT_SAT where ayear = 1442)) 
from Exams_GAT_SAT where  ayear = 1443)

An image has been attached showing what is required

Many thanks for helping me

Answer 1

Your example is wrong, but basically you can use LAG function to get the values from the previous row, then calculated the metric and after that update the table:

DROP TABLE IF EXISTS #DataSource;

CREATE TABLE #DataSource
(
    [id] INT
   ,[sch_id] INT
   ,[sch_name] VARCHAR(32)
   ,[ayear] INT
   ,[SAT_ay] DECIMAL(9,4)
   ,[SAT_target] DECIMAL(9, 4)
   ,[SAT_improve] DECIMAL(9, 4)
);

INSERT INTO #DataSource ([id], [sch_id], [sch_name], [ayear], [SAT_ay], [SAT_target], [SAT_improve])
VALUES (992, 290041, 'High School', 1442, 64.87, 68.3, NULL)
      ,(1533, 290041, 'High School', 1443, 63.733, 66.2, NULL);

WITH DataSorce ([id], [SAT_improve]) AS
(
    SELECT [id]
          ,([SAT_ay] - LAG([SAT_ay]) OVER (PARTITION BY [sch_id] ORDER BY [ayear])) /  (LAG([SAT_target]) OVER (PARTITION BY [sch_id] ORDER BY [ayear]) - LAG([SAT_ay]) OVER (PARTITION BY [sch_id] ORDER BY [ayear]))
    FROM #DataSource
)
UPDATE #DataSource
SET [SAT_improve] = ISNULL(DS2.[SAT_improve], 0)
FROM #DataSource DS1
INNER JOIN DataSorce DS2
    ON DS1.[id] = DS2.[id];

SELECT *
FROM #DataSource;