如何在表中填充错过的日期

update语句中的子选择需要限定两个entityid,否则它是不明确的。下面的查询将基于最近具有日期的实体更新每个空日期。

    UPDATE Entity
SET createdDate = DATEADD(SECOND, 
                    (SELECT TOP 1 entity.EntityID - em.EntityID FROM Entity em WHERE em.EntityID < (Entity.EntityID) AND em.createdDate IS NOT NULL ORDER BY EntityID DESC), 
                    (SELECT TOP 1 em.createdDate FROM Entity em WHERE em.EntityID < (Entity.EntityID) AND em.createdDate IS NOT NULL ORDER BY EntityID DESC))
WHERE createdDate IS NULL;

DECLARE @id INT,
@create_date DATETIME

    DECLARE upadte_records CURSOR FOR SELECT EntityID, createdDate from Entity
    OPEN upadte_records
    FETCH NEXT FROM upadte_records INTO @id, @create_date
    WHILE @@fetch_status <> -1
    BEGIN

            IF @create_date IS NULL
            BEGIN

                UPDATE Entity
                SET createdDate = DATEADD(SECOND, 1, (SELECT em.createdDate FROM Entity em WHERE em.EntityID = @id -1))
                WHERE EntityID = @id
            END


            FETCH NEXT FROM upadte_records INTO @id, @create_date
    END
    CLOSE upadte_records
    DEALLOCATE upadte_records