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?
Here each user posts are different so we can't apply any update command so we will use concat to add the site signature to each post kept inside a record field in our update command. Now let us see how it is used in a MySQL table query.
To do a conditional update depending on whether the current value of a column matches the condition, you can add a WHERE clause which specifies this. The database will first find rows which match the WHERE clause and then only perform updates on those rows.
The UPDATE statement in SQL is used to update the data of an existing table in database. We can update single columns as well as multiple columns using UPDATE statement as per our requirement. UPDATE table_name SET column1 = value1, column2 = value2,...
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;
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With