Update a column with it's concatenated previous value?

user_5341247 0
Viewed 104

Hello dear Stackoverflow SQL gurus.

Using this simple data model:

create table test(Id INT, Field1 char(1), Field2 varchar(max));

insert into test (id, Field1) values (1, 'a');
insert into test (id, Field1) values (2, 'b');
insert into test (id, Field1) values (3, 'c');
insert into test (id, Field1) values (4, 'd');

I'm able to update Field2 with Field1 and Field2 concatenated previous value in a simple TSQL anonymous block like this :

BEGIN 
    DECLARE @CurrentId INT;
    DECLARE @CurrentField1 char(1);
    DECLARE @Field2 varchar(max) = NULL;

    DECLARE cur CURSOR FOR   
        SELECT  id, Field1
        FROM    test
        ORDER BY id;

    OPEN cur  
    FETCH NEXT FROM cur INTO @CurrentId, @CurrentField1;
  
    WHILE @@FETCH_STATUS = 0  
    BEGIN
        SET @Field2 = CONCAT(@Field2, @CurrentId, @CurrentField1);

        UPDATE test
        SET Field2 = @Field2
        WHERE Id = @CurrentId;

        FETCH NEXT FROM cur INTO @CurrentId, @CurrentField1;
    END  
  
    CLOSE cur;
    DEALLOCATE cur;
END
GO

Giving me the desired result:

select * from test;

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

I want to achieved the same result with a single UPDATE command to avoid CURSOR. I thought it was possible with the LAG() function:

UPDATE test set Field2 = NULL; --reset data

UPDATE test
SET Field2 = NewValue.NewField2
FROM  (
    SELECT  CONCAT(Field2, Id, ISNULL(LAG(Field2,1) OVER (ORDER BY Id), '')) AS NewField2,
            Id
    FROM    test
) NewValue
WHERE   test.Id = NewValue.Id;

But this give me this:

select * from test;

Id  Field1  Field2
1   a       1
2   b       2
3   c       3
4   d       4

Field2 is not correctly updated with Id+Field1+(previous Field2). The update result is logic to me because when the LAG() function re-select the value in the table this value is not yet updated.

Do you think their is a way to do this with a single SQL statement?

sql-server sql
2 Answers

One method is with a recursive Common Table Expression (rCTE) to iterate through the data. This assumes that all values of Id are sequential:

WITH rCTE AS(
    SELECT Id,
           Field1,
           CONVERT(varchar(MAX),CONCAT(ID,Field1)) AS Field2
    FROM dbo.test
    WHERE ID = 1
    UNION ALL
    SELECT t.Id,
           t.Field1,
           CONVERT(varchar(MAX),CONCAT(r.Field2,t.Id,t.Field1)) AS Field2
    FROM dbo.test t
         JOIN rCTe r ON t.id = r.Id + 1)
SELECT *
FROM rCTe;

If they aren't sequential, you can use a CTE to row number the rows first:

WITH RNs AS(
    SELECT Id,
           Field1,
           ROW_NUMBER() OVER (ORDER BY ID) AS RN
    FROM dbo.Test),
rCTE AS(
    SELECT Id,
           Field1,
           CONVERT(varchar(MAX),CONCAT(ID,Field1)) AS Field2,
           RN
    FROM RNs
    WHERE ID = 1
    UNION ALL
    SELECT RN.Id,
           RN.Field1,
           CONVERT(varchar(MAX),CONCAT(r.Field2,RN.Id,RN.Field1)) AS Field2,
           RN.RN
    FROM RNs RN
         JOIN rCTe r ON RN.RN = r.RN + 1)
SELECT Id,
       Field1,
       Field2
FROM rCTe;
user_20299830

Unfortunately, SQL Server does not (yet) support string_agg() as a window function.

Instead, you can use cross apply to calculate the values:

select t.*, t2.new_field2
from test t cross apply
     (select string_agg(concat(id, field1), '') within group (order by id) as new_field2
      from test t2
      where t2.id <= t.id
     ) t2;

For an update:

with toupdate as (
      select t.*, t2.new_field2
      from test t cross apply
           (select string_agg(concat(id, field1), '') within group (order by id) as new_field2
            from test t2
            where t2.id <= t.id
           ) t2
     )
update toupdate
    set field2 = new_field2;

Here is a db<>fiddle.

Note: This works for small tables, but it would not be optimal on large tables. But then again, on large tables, the string would quickly become unwieldy.

user_11440350
Related