I want to update all the rows after the first row

问题:

I want to update all the rows after the first row for each Team.

TableName: Test

ID , Team , StartTime, EndTime, TotalTime
1....... A.........18:00.........20:00..........2:00
2....... B.........18:00.........20:00..........2:00
3........A.........18:00.........20:00..........2:00
4........F.........18:00.........20:00..........2:00
5........B.........18:00.........20:00..........2:00

 UPDATE TEST SET StartTime  =  DateAdd(SECOND, - ProjectedTime * 60, EndTime)

So here, I want to update row 3 and row 5 (as they are not first records based on team)

How do i achieve this?

回答1:

You can use CTE for this.

   with cte as
    (

    select test.*, row_number() over (partition by team order by ID) as teamRowNum
    from TEST
    )
     UPDATE cte SET StartDate  =  DateAdd(SECOND, - ProjectedTime * 60, EndDate)
    where teamRowNum > 1

回答2:

You can use a CTE with row_number():

with toupdate as (
      select t.*, row_number() over (partition by team order by id) as seqnum
      from test
     )
update toupdate
    set StartDate = DateAdd(SECOND, - ProjectedTime * 60, EndDate)
    where seqnum > 1;