Enhanced Update (patch) query

From: Davide:

[https://github.com/Azure/data-api-builder/blob/90e1bb077986a354c64d7b8013bc484ae5d7d2a8/src/Core/Resolvers/SqlMutationEngine.cs#L133](https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FAzure%2Fdata-api-builder%2Fblob%2F90e1bb077986a354c64d7b8013bc484ae5d7d2a8%2Fsrc%2FCore%2FResolvers%2FSqlMutationEngine.cs%23L133&data=05%7C02%7CSean.Leonard%40microsoft.com%7Ccfc636b1d07b4297fc3708dcd9ad4b71%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C638624585530661587%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=VKEry6aIym6RS5eNyDnU2x2p4I00zQFdaBIp72%2Fy90U%3D&reserved=0)
[Implementing an Implicit Transaction using Transaction Scope - .NET Framework | Microsoft Learn](https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Flearn.microsoft.com%2Fen-us%2Fdotnet%2Fframework%2Fdata%2Ftransactions%2Fimplementing-an-implicit-transaction-using-transaction-scope&data=05%7C02%7CSean.Leonard%40microsoft.com%7Ccfc636b1d07b4297fc3708dcd9ad4b71%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C638624585530682557%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=P77%2Fq25K%2B%2FzKiyRkmfpbuWkrL0tzW6Zf8%2BAdZ13Rn48%3D&reserved=0)

```tsql
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:

```tsql
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
```

Start with src/Core/Resolvers/SqlMutationEngine.cs at the linked line and trace how the SQL for update mutations is assembled. Review the proposed count query and transaction behavior against the generated SQL shown here; done means the update operation consistently uses the intended row count and transaction semantics, with coverage added where the existing mutation tests apply.

Vom Indexierungsmodell aus dem Issue-Text verfasst.