Point of update when using case in an Update statement

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.

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.

I agree with the above answers, consider this example

 DECLARE @temp TABLE (ID INT ,  Amt INT,  Dt DATE)  INSERT INTO @temp  SELECT 1,1000,GETDATE()

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

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

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