Запрос на изменения полей у записей с некоторым признаком

 [dbo].[MediaFile](
    [Id] [INT] IDENTITY(1,1) NOT NULL,
    [Url] [nvarchar](MAX) NULL,
    [FileName] [nvarchar](MAX) NULL,
    [ContentType] [nvarchar](MAX) NULL,
    [CreationDate] [datetime2](7) NOT NULL,
    [DisplayNumber] [INT] NOT NULL,
    [TYPE] [INT] NOT NULL,
    [CompanyId] [INT] NULL,
    [TradeLotId] [INT] NULL)

ALTER PROCEDURE [dbo].[EditDisplayNumberOnMedialFile]
AS
BEGIN
 
DECLARE @MediaFileTable TABLE (Id INT, TradeLotId INT, DisplayNumber INT)
 
DECLARE @MediaFileTableNew TABLE (Id INT, TradeLotId INT, DisplayNumber INT)
 
INSERT @MediaFileTable
SELECT Id, TradeLotId, DisplayNumber
FROM [MediaFile]
WHERE TradeLotId IN (SELECT TradeLotId 
                      FROM [MediaFile] 
                      GROUP BY TradeLotId 
                      HAVING COUNT(DisplayNumber) > 1)
 
DECLARE @lotId INT, @prevLotId INT, @NUMBER INT, @displayNumber INT, @recordId INT
SET @NUMBER = 0
SET @displayNumber = 0
 
while EXISTS (SELECT * FROM @MediaFileTable)
BEGIN
 
    SELECT top 1 @lotId = TradeLotId, @displayNumber = DisplayNumber, @recordId = Id
    FROM @MediaFileTable
    ORDER BY TradeLotId ASC
 
    IF (@prevLotId <> @lotId)
    BEGIN
     SET @prevLotId = @lotId
     SET @NUMBER = 0
    END
    ELSE
    BEGIN
        --INSERT @MediaFileTableNew (Id, TradeLotId, DisplayNumber) VALUES (@recordId, @lotId, ++@number)
        SET @NUMBER = @NUMBER + 1
        UPDATE [MediaFile] SET DisplayNumber = @NUMBER WHERE Id = @recordId
    END
 
    DELETE @MediaFileTable
    WHERE Id = @recordId
 
END
 
END

CREATE TABLE mytable (id INT,  TradeLotId INT, DisplayNumber INT);
 
INSERT INTO mytable VALUES (1,1,0),(2,1,0),(3,2,0),(4,2,0),(5,2,0)
 
WITH cte AS
  (SELECT *,ROW_NUMBER() OVER(partition BY TradeLotId ORDER BY id) num FROM mytable)
UPDATE cte SET DisplayNumber=num;
 
SELECT * FROM mytable