I was faced with a problem where I had to do a price update based on some product replacement logic.

This meant that I needed to calculate an existing price and compare it to the replacement product’s price and whichever was highest, needed to be written into the final structure.
Having to do this in an update query which could number the thousands of records, it presented the problem of how to get the calculating logic into the update query without having to resort to cursors and without having to call the calculating function too many times for each row.

A solution presented itself because I knew that I could make assignments in UPDATE statements and reuse that assignment as well, so I could make the calculation into a function which would assign a variable, and then I could case this variable and decide. This meant I only had to call the calculation function once, and could use the result for multiple things.

The technique is easy to illustrate using this simplified made up example:


--I'm using a unique identifier to mimic the result of my function.
DECLARE @testTable TABLE (idx INT, updatedValue UNIQUEIDENTIFIER)
--used in the update to assign a value to.
DECLARE @ui UNIQUEIDENTIFIER
--insert some test data
INSERT INTO @testTable
( idx, updatedValue )
VALUES ( 1, -- idx - int
NULL  -- updatedValue - uniqueidentifier
),
( 2, -- idx - int
NEWID()  -- updatedValue - uniqueidentifier
),
( 3, -- idx - int
NULL  -- updatedValue - uniqueidentifier
)
--verify inserted data
SELECT * FROM @testTable AS TT
--now, do the update, by first assigning a new value to @ui
--from a function, and then use that value to compare to the content of @testTable
UPDATE @testTable
SET @ui = NEWID(),
updatedValue = CASE
WHEN updatedValue IS NULL THEN @ui
ELSE updatedValue
END
--verify updated data
SELECT * FROM @testTable AS TT
--notice that row idx 1 and 3 have receieved new updatedValue,
--whereas idx 2 has retained its value.

It’s a technique you wouldn’t really expect from SQL when knowing how it handles sets based execution, but well – it seems to work.

Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>