使用基于ID的增量日期更新列

使用与Larnu类似但更直接的方法,可以直接更新CTE:

WITH cte AS
(
    SELECT  *
    ,       ROW_NUMBER() over (order by id) rn
    FROM    T1
)

UPDATE cte
    SET [date] = DATEADD(DAY, rn, GETDATE())

这就是你要找的吗?根据ID更新日期列

    update T1
    set [Date]= GetDate() + (id-1)
    from T1

一种方法是使用CTE评估新日期 ROW_NUMBER 和 DATEADD 然后 UPDATE 使用CTE中的值:

USE Sandbox;
GO

CREATE TABLE TestTable (ID int IDENTITY, SomeDate date)
INSERT INTO dbo.TestTable (SomeDate)
VALUES (GETDATE()),(GETDATE()),(GETDATE()),(GETDATE()),(GETDATE());
GO

SELECT *
FROM TestTable;
GO
WITH NewDates AS(
    SELECT ID, DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY TT.ID), GETDATE()) AS NewDate
    FROM dbo.TestTable TT)
UPDATE TT
SET SomeDate = ND.NewDate
FROM dbo.TestTable TT
     JOIN NewDates ND ON TT.ID = ND.ID;
GO
SELECT *
FROM TestTable;

GO
DROP TABLE TestTable;
GO

使用 dateadd

    update T2     
    SET  T2.D=dateadd(dd,T3.rn,getdate())
      FROM 
      T1 as t2 INNER JOIN 
      (
      select *,ROW_NUMBER() over (order by [id]) as rn from T1
      ) T3 ON t2.id=t3.id

使用 recursive cte 生成 Id 和 dates 然后 join 您的表基于 身份证件 更新日期的列

with cte as ( 
  select 1 AS Id, getdate() as dt
  union all
  select cte.Id+1, dateadd(d,1,cte.dt) 
  from   cte 
  where  cte.Id < (SELECT MAX(ID) FROM T1)
)
UPDATE T1
SET T1.Date = cte.dt
FROM T1 INNER JOIN cte ON T1.Id = cte.Id

你不能用 Windowed functions 直接在更新查询中。你应该把逻辑存储在 temporary table 然后在更新查询中使用该值。有一种方法可能如下。

 ;with cte as
    (
     select id,Invoice,dateadd(DAY, ROW_NUMBER() over (order by id), GetDate()) as dt
    from T1
    )
    update a
    set a.[date]= cte.dt
    from T1  a join cte on cte.id=a.id
    where a.id=cte.id