CODEWITHSUNDEEP
sqlsql-server
Simão Santos
Simão Santos

Reputation: 21

SQL - How to update same row multiple times

I want to update a row multiple times.

Example:

DECLARE @Formula TABLE
(
    Formula NVARCHAR(MAX)
)

INSERT INTO @Formula
SELECT '$v1$ + $v2$ + $v3$'

DECLARE @VariableValues TABLE
(
    Variable NVARCHAR(MAX),
    [Value] DECIMAL(16, 3)
)

INSERT INTO @VariableValues
SELECT '$v1$', 12 UNION
SELECT '$v2$', 14 UNION
SELECT '$v3$', 18

UPDATE f
SET Formula = REPLACE(Formula, Variable, [Value])
FROM @Formula f
CROSS JOIN @VariableValues vv

SELECT * FROM @Formula

So in the example case, I would want to update the formula 3 times, replacing each variable with its value.

What actually happens is that only the first variable gets replaced.

Is this possible?

Upvotes: 0

Views: 954

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Use aggregation:

UPDATE t
    SET Id = t.Id + t2.sum_id
    FROM @TEST t CROSS JOIN
         (SELECT SUM(t2.id) as sum_id
          FROM @TEST t2
         ) t2;

UPDATE updates a given row only once. You need to precalculate the result using aggregation.

Upvotes: 1

Related Questions