UPDATE Statement with concatenate row values

Question

The database table having Column1 and Column2. Fetch the values and map the column (Column1 and Column2) details and update in Column3

Column3 should have Column1 value where the Column1 value is equal to Column2 value. Please refer the attached image for better understanding.

Example1: Column1 holds 914702(Row#2) the same value is available in Column2(Row#1). So the Column3 for Row#2 should update the value of Column1 in Row#1 which is 914703

Example2: Column1 holds 914698(Row#5) the same value is available in Column2(Row#3 & 4). So the Column3 in Row#5 should holds the values of Column1 in Row#3 & 4 which is 914700, 914701.

Able to achieve this with below mentioned query and WHILE loop.

The table having 100s of Rows. Using while loop will impact the performance. Will we able to achieve this with UPDATE statement in SQL Server.

DECLARE @Variable VARCHAR(MAX) = ''
SELECT @Variable = @Variable + 'Value: ' + CONVERT(VARCHAR, Column1) + ', ' 
FROM Table1 WHERE Column2 = @Value

UPDATE Table1
SET Column3 = SUBSTRING(@Variable, 0, LEN(@Variable) - 1)
WHERE Column1 = @Value

Expected Output

It's possible, but looks like you really need to normalize your tables a bit more. Lookup 3rd Normal Form — Charlieface, Jan 05 '21 at 17:01
Please read [this](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. Then see [`String_Agg`](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15). Then update SQL Server. — HABO, Jan 05 '21 at 17:29

score 1 · Accepted Answer · answered Jan 05 '21 at 19:48

That script should work for you:

use tempdb;
GO
DROP TABLE IF EXISTS Table1;
GO
CREATE TABLE Table1 (
    Column1 INT NOT NULL,
    Column2 INT NULL,
    Column3 VARCHAR(MAX) NULL
);
GO
INSERT INTO Table1 VALUES 
(914703,914702, Null),
(914702,Null, Null),
(914700,914698, Null),
(914701,914698, Null),
(914698,Null, Null),
(914570,Null, Null),
(914569,Null, Null),
(914568,Null, Null),
(914565,914564, Null),
(914564,Null, Null),
(914546,Null, Null),
(914545,Null, Null),
(914425,Null, Null);
GO
UPDATE te 
SET Column3 = IIF(Column3_n IS NULL, NULL, LEFT(Column3_n,LEN(Column3_n)-1))
FROM Table1 as te
OUTER APPLY (
    SELECT 'Value: ' + CAST(t2.Column1 as VARCHAR(MAX)) + ', '
    FROM Table1 as t1
    INNER JOIN Table1 as t2 
        ON t1.Column1 = t2.Column2
    WHERE t1.Column1 = te.Column1
    FOR XML PATH('')
) as ta(Column3_n);
GO
SELECT * FROM Table1 ORDER BY Column1 DESC;
GO

UPDATE Statement with concatenate row values

1 Answers1