CODEWITHSUNDEEP
sqlsql-serverentity-framework-coresql-updateef-code-first
htp15
htp15

Reputation: 71

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

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.

Upvotes: 2

Views: 564

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

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;

Upvotes: 1

Related Questions