Point of update when using case in an Update statement

hi there,

I have cut this down to keep it simple but here goes...

I have a table with 4 columns - OrderID, InitialOrderAmount, RemainingOrderAmount, CompletedDate

Rule is when the remaining order amount is 0, set the completed date.

I have a stored proc that takes in the ID, and the an order amount filled so I want to substract the amount filled from the remaining amount and if its 0 set the completed date.

my code is something like this...

UPDATE [OrderTable] SET RemainingOrderAmount = RemainingOrderAmount - ABS(@orderAmountFilled ),        -- reduce the amount left to close
                  ClosingTime = (CASE WHEN (RemainingOrderAmount - ABS(@orderAmountFilled)) = 0 THEN GETUTCDATE()
                              ELSE NULL END)                                                              
    WHERE OrderID = @orderID

ok so what I believed would happen is that even though the update statement updates the RemainingOrderAmount first, the case statement has to test by subtracting the amount as the first part of the update hasnt been committed... so I need the current value and subtract the amount filled.

Is this correct.

The reason I ask, is that in a test run... 13000 records updated fine but 4 had the maths right in that the RemainingOrderAmount was set to 0 but the completed date didnt get set.

Any ideas... thanks Mick
more ▼

asked Oct 26, 2010 at 05:01 AM in Default

Doiremik gravatar image

71 6 6 7

looks like it should work, is there more to this that might be affecting the result?
Oct 26, 2010 at 05:32 AM Scot Hauder
(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first
It's all part of a single transaction, so the values are not being modified first, they're being modified at the same time. Any reads are gathered and you won't see the affects of writes until they're committed. You'd probably need to break this down into two statements within a transaction to make it work.
more ▼

answered Oct 26, 2010 at 05:03 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.9k 19 21 74

@Grant +1 - as you said, the values are both calculated at the same time and then updated.

@Doiremik - This can be seen in the execution plan, there would be a table scan/clustered index scan/index seek or similar to find the OrderId, then a calculate scalar (with ALL values being calcualted at the same time) and then the update of the table (and any indexes that needed it too). Could you be running into concurrency issues?
Oct 26, 2010 at 06:05 AM WilliamD
(comments are locked)
10|1200 characters needed characters left
Thinking about it some more, it might even be a valid place for a trigger, although, I'd still rather see it in two, controlled steps. I'm not a fan of triggers.
more ▼

answered Oct 26, 2010 at 05:04 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.9k 19 21 74

(comments are locked)
10|1200 characters needed characters left

I agree with the above answers, consider this example


UPDATE @temp SET Amt = Amt+1000, Dt = CASE WHEN Amt = 2000 THEN NULL ELSE GETDATE()END

SELECT * FROM @temp --Result------------ ID Amt Dt 1 2000 2010-10-26

If the values are not modified together then the result Dt Column should be updated with NULL.
more ▼

answered Oct 26, 2010 at 05:11 AM

Cyborg gravatar image

10.6k 36 40 45

(comments are locked)
10|1200 characters needed characters left

thanks lads. yes indeed I did think on a trigger but the actual implementation is a bit more complex and thats why I do it in the update instead of a trigger as I have some more business rules.

I just want to check was there some mad bug or something that I didnt know about. I have had 13000 records all get updated and set correctly except 4 of them and they are the same as the rest.

@Grant. Yes indeed Grant I did think there was a concurrency issue but when I checked the logs I keep, I'm only updating this one row at a time as I use the PK ID to do it and its only in my logs once. I also catch exceptions if there is a lock and then retry the operation and this looks clean to.

However Grant, is it not the same code if I break it out into two statements except that I have to do a round trip to the database. Thats why I built it into the case, instead of another update statement.

Worst thing is, that its obviously a fluky thing and trying to track this down will be fun.

@Cyborg - nice clean example cyborg... thanks for the confirmation also regards Mick
more ▼

answered Oct 26, 2010 at 07:14 AM

Doiremik gravatar image

71 6 6 7

(comments are locked)
10|1200 characters needed characters left

ok, you asked for it lol..

here is the actual statement. It does basically what the other simple statement does. I get in a value which can be negative or positive but I just use the abs on it. I'm closing out volume and in this case I know its a close.

So I subtract the @VolumentAmount from the amount closing.. add it to the amount closed.. do some working out on pricing etc and then set the ClosingTime. The worst thing is that all the other values are all correct for the 4 values that didnt have their ClosingTime set when it should have.

I have this enclosed in a try catch also so I would hope any locks or concurrency issues would be caught. The reason I dont think its concurrency is that the other values are all correct and I get no errors. I also know that only one call was made to update this record.

So should I put in an error check and test if the date is not null and throw an exception. Just for the craic, I'm going to run the values through the database to test... might take an hour or so... thanks again... a very confussed Mick

-- Dont reduce the amount of VolumeOpened by the amount closed as it has already been reduced on the ScaleOutPosition. -- Position is closed when Opened = Closed

UPDATE [Position] SET VolumeClosing = VolumeClosing - ABS(@VolumeAmount),      -- reduce the amount left to close
                  VolumeClosed = VolumeClosed + ABS(@VolumeAmount),          -- add to the amount Closed
                  FilledReturnValueOnClosing = FilledReturnValueOnClosing + (ABS(@VolumeAmount) * @ActualMarketExecutionPrice),
                  QuotedMarketAskCostOnClosing = (CASE WHEN AccountBaseCurrency != SUBSTRING(Asset,5,3)          -- if we are closing a long position and the Base currency doesnt match the Base Asset then we need to store the ask for conversions in RPL
                                                 THEN (QuotedMarketAskCostOnClosing + (ABS(@VolumeAmount) * @QuotedMarketAskRate)) 
                                                 ELSE QuotedMarketAskCostOnClosing END), -- track the ask price. Used for RPL and only set on closing. Will be the same price as @ActualMarketExecutionPrice for long positions                               
                  ClosingTime = (CASE WHEN (VolumeClosed + ABS(@VolumeAmount)) = TotalVolumeEverOpened THEN GETUTCDATE()
                              ELSE NULL END)                                                              
    WHERE PositionID = @positionID
more ▼

answered Oct 26, 2010 at 07:34 AM

Doiremik gravatar image

71 6 6 7

@Doiremik - is this happening consistently with just 4 records?

If so, have you tried just selecting the values you show here instead of updating? I would be interested to see the result of that.

Another question, why update one at a time, can't you do this in one? Maybe that could be a change enough to help out
Oct 26, 2010 at 07:46 AM WilliamD

@William - no william, this is intermittent from what I can see. If I select them, I would need to pump them to a file or something and then wait for the issue to arise again. As for updating one at a time, what actally happens is, I have a broker system that recieves fills for orders. They might be partially filled in which case only when there is nothing left to fill I set the Closed Time, if they get filled completely on the one go I update the closed Time on the order. So its handling orders as they stream into the system and I dont get a batch in the one go... if this makes sense...

regards Mick
Oct 26, 2010 at 08:08 AM Doiremik
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 26, 2010 at 05:01 AM

Seen: 1403 times

Last Updated: Oct 26, 2010 at 12:20 PM