Enhanced Update (patch) query

[seantleonard]

From: Davide:

https://github.com/Azure/data-api-builder/blob/90e1bb077986a354c64d7b8013bc484ae5d7d2a8/src/Core/Resolvers/SqlMutationEngine.cs#L133 Implementing an Implicit Transaction using Transaction Scope - .NET Framework | Microsoft Learn

 DECLARE @ROWS_TO_UPDATE int;

 SET @ROWS_TO_UPDATE = (SELECT COUNT(*) as cnt_rows_to_update FROM [dbo].[todo] WHERE [dbo].[todo].[id] = @param0);
 SELECT COUNT(*) as cnt_rows_to_update FROM [dbo].[todo] WHERE [dbo].[todo].[id] = @param0; 

 IF @ROWS_TO_UPDATE = 1 BEGIN

 UPDATE [dbo].[todo] SET [dbo].[todo].[position] = @param1, [dbo].[todo].[owner_id] = @param2

 OUTPUT Inserted.[id] AS [id], Inserted.[title] AS [title], Inserted.[completed] AS [completed], Inserted.[owner_id] AS [owner_id], Inserted.[position] AS [order]

 WHERE [dbo].[todo].[id] = @param0;

 END

Which I think can be improved in two ways:

There are two SELECT COUNT(*) operations done. The second could just be a SELECT @ROWS_TO_UPDATE, right? I think I have already mentioned this but let’s make sure the whole operation is within a transaction with a SERIALIZABLE transaction level as otherwise it is not guaranteed that the set of data on which the UPDATE operates is the same of the one that was seen by the SELECT COUNT. Even better, I think, we could just use the READ COMMITTED transaction level the default) and then use an UPDLOCK hint:

SET XACT_ABORT ON

BEGIN TRAN

DECLARE @ROWS_TO_UPDATE int;

SET @ROWS_TO_UPDATE = (SELECT COUNT(*) as cnt_rows_to_update FROM [dbo].[todo] WITH (UPDLOCK) WHERE [dbo].[todo].[id] = @param0);

SELECT @ROWS_TO_UPDATE AS cnt_rows_to_update;

IF @ROWS_TO_UPDATE = 1 BEGIN
 UPDATE [dbo].[todo] SET [dbo].[todo].[position] = @param1, [dbo].[todo].[owner_id] = @param2

 OUTPUT Inserted.[id] AS [id], Inserted.[title] AS [title], Inserted.[completed] AS [completed], Inserted.[owner_id] AS [owner_id], Inserted.[position] AS [order]

 WHERE [dbo].[todo].[id] = @param0;
END
COMMIT TRAN