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.