如何使用单条SQL UPDATE语句实现字段的累积拼接更新（替代游标方案）

Hey there! Great question—ditching cursors for this kind of cumulative update is totally feasible, and your instinct to explore window functions makes sense, even if LAG() didn't pan out as expected. Let's break down what went wrong, then walk through a couple of reliable solutions.

Why Your LAG() Approach Failed #

You're exactly right about the root cause: window functions like LAG() operate on a snapshot of the original data at the start of the query. Since you reset Field2 to NULL before the update, every LAG(Field2) call returns NULL, so your CONCAT ends up just being Id (since CONCAT(NULL, Id, ...) ignores the NULL). There's no way to get the updated, cumulative value from a window function here because they don't track row-by-row changes during execution.

Solution 1: Recursive CTE (Works with All SQL Server Versions) #

A recursive Common Table Expression (CTE) is perfect for this kind of sequential, cumulative work. It builds up the concatenated string row by row, just like your cursor—but without the cursor overhead.

If Your Id Values Are Continuous #

WITH CumulativeData AS (
    -- Anchor: The first record (smallest Id)
    SELECT 
        Id, 
        Field1, 
        CONVERT(VARCHAR(MAX), CONCAT(Id, Field1)) AS CumulativeField2
    FROM test
    WHERE Id = (SELECT MIN(Id) FROM test)
    
    UNION ALL
    
    -- Recursive step: Add each subsequent record's value to the cumulative string
    SELECT 
        t.Id, 
        t.Field1, 
        CONVERT(VARCHAR(MAX), cd.CumulativeField2 + CONCAT(t.Id, t.Field1)) AS CumulativeField2
    FROM test t
    JOIN CumulativeData cd ON t.Id = cd.Id + 1
)
UPDATE t
SET Field2 = cd.CumulativeField2
FROM test t
JOIN CumulativeData cd ON t.Id = cd.Id;

If Your Id Values Are Not Continuous #

If your Ids might have gaps, use ROW_NUMBER() to create a sequential numbering first:

WITH NumberedTest AS (
    SELECT 
        Id, 
        Field1, 
        ROW_NUMBER() OVER (ORDER BY Id) AS RowNum
    FROM test
),
CumulativeData AS (
    -- Anchor: The first row in ordered sequence
    SELECT 
        Id, 
        Field1, 
        RowNum,
        CONVERT(VARCHAR(MAX), CONCAT(Id, Field1)) AS CumulativeField2
    FROM NumberedTest
    WHERE RowNum = 1
    
    UNION ALL
    
    -- Recursive step: Append each next row's value
    SELECT 
        nt.Id, 
        nt.Field1, 
        nt.RowNum,
        CONVERT(VARCHAR(MAX), cd.CumulativeField2 + CONCAT(nt.Id, nt.Field1)) AS CumulativeField2
    FROM NumberedTest nt
    JOIN CumulativeData cd ON nt.RowNum = cd.RowNum + 1
)
UPDATE t
SET Field2 = cd.CumulativeField2
FROM test t
JOIN CumulativeData cd ON t.Id = cd.Id;

Solution 2: FOR XML PATH (Legacy SQL Server Friendly) #

If you're working with SQL Server 2016 or earlier (before STRING_AGG was introduced), you can use FOR XML PATH to concatenate all prior records into a single string for each row:

UPDATE t
SET Field2 = (
    -- Concatenate Id + Field1 for all rows with Id <= current row's Id
    SELECT CONCAT(Id, Field1)
    FROM test t2
    WHERE t2.Id <= t.Id
    ORDER BY t2.Id
    FOR XML PATH(''), TYPE
).value('.', 'VARCHAR(MAX)') -- Convert XML result back to plain text
FROM test t;

Solution 3: STRING_AGG with Window Frame (SQL Server 2022+) #

If you're on SQL Server 2022 or later, you can use STRING_AGG with a window frame to directly compute the cumulative string. Note that STRING_AGG supports window frames in newer versions:

WITH CumulativeData AS (
    SELECT 
        Id,
        STRING_AGG(CONCAT(Id, Field1), '') OVER (
            ORDER BY Id 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS CumulativeField2
    FROM test
)
UPDATE t
SET Field2 = cd.CumulativeField2
FROM test t
JOIN CumulativeData cd ON t.Id = cd.Id;

Verify the Result #

After running any of these solutions, executing SELECT * FROM test; will give you the expected output:

Id Field1 Field2
1  a      1a
2  b      1a2b
3  c      1a2b3c
4  d      1a2b3c4d

内容的提问来源于stack exchange，提问作者SuperPoney