SQL Server : calculate column value based on value in previous row/id

I have what I hope is a simple problem to solve, but one that is puzzling me. My table can be created with the below sql:

CREATE TABLE test
(
     [Id] [int] IDENTITY(1, 1) NOT NULL, 
     [lang] varchar(13), 
     [TotalProjectCost] [decimal](16, 2)
);

INSERT INTO test ([lang], [TotalProjectCost])
VALUES
    ('en',  CAST(341412.00 AS Decimal(16, 2))),
    ('es-mx',  CAST(342.00 AS Decimal(16, 2))),
    ('en',  CAST(341412.00 AS Decimal(16, 2))),
    ('es-mx',  CAST(34.00 AS Decimal(16, 2))),
    ('en',  CAST(341412.00 AS Decimal(16, 2))),
    ('es-mx',  CAST(34.00 AS Decimal(16, 2)));

Which looks like this:

This is basically a currency conversion task (the costs in the es-mx row are arbitrary in this example). What I wish to do is update all the TotalProjectCost values of rows with lang of ‘es-mx’: for these rows, I wish to calculate the value as whatever the TotalProjectCost value is of the previous ‘en’ row * 15.8735.

The way my actual table is set up, values are always submitted in pairs (one en, one es-mx)…so odd Ids should always be en, even ones should always be es-mx. As you can see, there’s probably tons of ways to approach this and maybe that’s why it’s hard for me to know where to start.

I was kinda getting somewhere with this select statement:

select * 
from test as table1
inner join test as table2 on table1.ID = (table2.ID + 1)

But I still fall short of actually getting to the Update that calculates the correct values.

I appreciate any thoughts on this! I’ve tried looking around for an example on the boards that somewhat matches my scenario, but they seem a bit more involved than what I’m trying to accomplish.

Answer

Use LAG() widow function in an updatable CTE:

WITH cte AS (
  SELECT *, LAG([TotalProjectCost]) OVER (ORDER BY [Id]) prev_cost 
  FROM test
)
UPDATE cte 
SET [TotalProjectCost] = 15.8735 * prev_cost
WHERE [lang] = 'es-mx'

See the demo.
Results:

> Id | lang  | TotalProjectCost
> -: | :---- | ---------------:
>  1 | en    |        341412.00
>  2 | es-mx |       5419403.38
>  3 | en    |        341412.00
>  4 | es-mx |       5419403.38
>  5 | en    |        341412.00
>  6 | es-mx |       5419403.38