Lag function on an update not working

  • I have a table with odds from sportsbook, and I need to populate a gamedate value that is null . The gamedate in question coincides with the even numbered rotation rows of the data, below are samples of what i'm working with

    Create [dbo].gameresults (

    gamedate date null,

    team varchar(50) null,

    rotation int null,

    odds float null

    )

    insert into gameresults(gamedate,team,rotation,odds) values ('2023-10-1','NYJ',101,null)

    insert... values (null,'DET',102,-7)

    insert... values ('2023-10-1','LAR',103,-1)

    insert... values (null,'PGH',104,null)

    insert... values ('2023-10-2',WAS',205,null)

    insert...values(null,'PHI',206,-6.5)

    using the lag function, i'm using this update script to try to put a value in the gamedate of the ones that are null:

    update [dbo].[gameresults] set [gamedate] = (select LAG([gamedate],1) OVER (ORDER BY [rotation]) where rotation%2 = 1) where [gamedate] is null

    each of the gamedates that are null are guaranteed to have a rotation number that is EVEN

    when running the query, the system says the rows have been updated, but checking the data, they are still null. The order by I am assigning rotation

    Any help greatly appreciated!

  • If you convert your UPDATE to a SELECT, you will see that you cannot filter the way that you are trying.  The LAG works within your filtered results.

    SELECT    g.gamedate
    , g.team
    , g.rotation
    , g.odds
    , lagDate = (select LAG(g.gamedate,1) OVER (ORDER BY g.rotation) where rotation%2 = 1)
    FROM dbo.gameresults AS g
    where g.gamedate is NULL;

    To achieve what you are attempting, you need to create a CTE with the calculate column, and update the CTE.

    WITH cteData AS (
    SELECT g.gamedate
    , lagDate = LAG( g.gamedate, 1 ) OVER ( ORDER BY g.rotation )
    FROM dbo.gameresults AS g
    )
    UPDATE cteData
    SET gamedate = lagDate
    WHERE gamedate IS NULL;
  • duplicate post deleted

  • Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply