Update each row in table

Question

I have a table with some value columns, a formula, and a result column.

|rownum|value1|value2|value3|formula             |result|
|------|------|------|------|--------------------|------|
|1     |11    |30    |8     |value1/value2*value3|      |
|2     |43    |0     |93    |value1-value2+value3|      |

I want to populate the result column with the result of the formula.

Currently I'm doing that with this query:

DECLARE @v_sql NVARCHAR(MAX) 

SET @v_Sql = CAST ((SELECT
           ' UPDATE [table] ' +
            ' SET [result] = ' + table.[formula] +
            ' WHERE [rownum] = ' + CAST(table.[rownum] as nvarchar(255)) +
            ';'
          FROM [table]
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') AS NVARCHAR (MAX))

EXEC (@v_Sql)

The problem is that this takes a very long time. The # rows in the table will be 5 - 10 million.

Is there any way to speed this up? Of alternative approaches to this problem?

Many thanks!

Answer 1

Assuming operator order rules and covering only your simple formula example:

UPDATE [table]
SET  [result] = case replace(replace(replace([formula],'value1', ''), 'Value2', ''), 'Value3', '')
        when '++' then [value1] + [value2] + [Value3]
        when '+-' then [value1] + [value2] - [Value3]
        when '+*' then [value1] + [value2] * [Value3]
        when '+/' then [value1] + [value2] / [Value3]
        when '-+' then [value1] - [value2] + [Value3]
        when '--' then [value1] - [value2] - [Value3]
        when '-*' then [value1] - [value2] * [Value3]
        when '-/' then [value1] - [value2] / [Value3]
        when '*+' then [value1] * [value2] + [Value3]
        when '*-' then [value1] * [value2] - [Value3]
        when '**' then [value1] * [value2] * [Value3]
        when '*/' then [value1] * [value2] / [Value3]
        when '/+' then [value1] / [value2] + [Value3]
        when '/-' then [value1] / [value2] - [Value3]
        when '/*' then [value1] / [value2] * [Value3]
        when '//' then [value1] / [value2] / [Value3]
        end
from [Table]