Code First Migration - SQL Update column with data from another row in same table

Question

Situation:

Given is the following table:

Id	Price	ProductId	ValidFrom
1	1,00	1	01-01-01
2	1,00	1	01-01-15
3	1,00	1	01-02-01
4	1,00	2	01-01-01
5	1,00	2	01-01-11
6	1,00	2	01-01-28
7	1,00	2	01-02-01
8	1,00	2	01-01-08

Now I want to add a new column "ValidUntil" to the table. Because Data already exist the new column should be prefilled for existing entries.

The table should look like this after the change:

Id	Price	ProductId	ValidFrom	ValidUntil
1	1,00	1	01-01-01	01-01-15
2	1,00	1	01-01-15	01-02-01
3	1,00	1	01-02-01
4	1,00	2	01-01-01	01-01-11
5	1,00	2	01-01-11	01-01-28
6	1,00	2	01-01-28	01-02-01
7	1,00	2	01-02-01	01-02-08
8	1,00	2	01-02-08

I use Entity Framework Core Code First Migration to update the data.

Problem:

I rename the column first, thereafter I use the following line to prefill the column:

migrationBuilder.Sql("UPDATE Prices p1 SET p1.ValidUntil = (SELECT TOP 1 ValidFrom FROM Prices p2 WHERE p2.ValidFrom > p1.ValidFrom ORDER BY DATEDIFF(day, p2.ValidFrom, p1.ValidFrom))");

Runing the update I get the following errors:

Incorrect syntax near 'p1'.

Incorrect syntax near ORDER-Keyword.

Question:

I have less experience with SQL Statements so I would appreciate any help to understand what's wrong with this statement.

Also if anyone has a statement which would fit better for this case he is welcome too.

Thank you for your help.

Answer 1

You can use lead() to get the until date:

select p.*,
       lead(validfrom) over (partition by productid order by validfrom) as validuntil
from prices p;

In SQL Server, you can incorporate this into an update:

with toupdate as (
      select p.*,
             lead(validfrom) over (partition by productid order by validfrom) as new_validuntil
      from prices p
     )
update toupdate
    set validuntil = new_validuntil
    where new_validuntil is not null;