Dlaczego moje zapytanie SQL Server zachowuje się inaczej w przypadku UPDATE niż w SELECT?

Napisałem zapytanie do programu SQL Server, które aktualizuje rekordy, aby miały numer kolejny po podzieleniu na partycje w polu. Po uruchomieniu go jako instrukcji SELECT wszystko wygląda świetnie:

DECLARE @RunDetailID INT = 448
DECLARE @JobDetailID INT

SELECT @JobDetailID = [JobDetailID] FROM [RunDetails] WHERE [RunDetailID] = @RunDetailID

SELECT
    [OrderedRecords].[NewSeq9],
    RIGHT([OrderedRecords].[NewSeq9], 4)
FROM
    (
        SELECT
            [Records].*,
            [Records].[SortField] + RIGHT('0000' + CAST(ROW_NUMBER() OVER(PARTITION BY [Records].[SortField] ORDER BY [Records].[RunDetailID], [Records].[SortField], [Records].[PieceID]) AS VARCHAR), 4) NewSeq9
        FROM
        (
            SELECT
                [MRDFStorageID], 
                [RunDetailID], 
                [SortField], 
                [PieceID], 
                [Seq9], 
                [BallotType]
            FROM
                [MRDFStorage]
                    JOIN [BallotStyles] ON [MRDFStorage].[SortField] = [BallotStyles].[Style] and [BallotStyles].[JobDetailID] = @JobDetailID
            WHERE
                [RunDetailID] IN (SELECT [RunDetailID] FROM [RunDetails] WHERE [JobDetailID] = @JobDetailID AND [RunStatusID] <> 0)
        ) Records
    ) OrderedRecords 
        JOIN MRDFStorage ON [OrderedRecords].[MRDFStorageID] = [MRDFStorage].[MRDFStorageID]
WHERE
    [MRDFStorage].[RunDetailID] = @RunDetailID

Jednak gdy przekształcam zapytanie w polecenie UPDATE, zaczyna ono pomijać liczby parzyste:

DECLARE @RunDetailID INT = 448
DECLARE @JobDetailID INT 

SELECT @JobDetailID = [JobDetailID] FROM [RunDetails] WHERE [RunDetailID] = @RunDetailID

UPDATE
    [MRDFStorage]
SET
    [Seq9] = [OrderedRecords].[NewSeq9],
    [Overlay1] = [OrderedRecords].[NewSeq9],
    [Overlay10] = RIGHT([OrderedRecords].[NewSeq9], 4)
FROM
    (
        SELECT
            [Records].*,
            [Records].[SortField] + RIGHT('0000' + CAST(ROW_NUMBER() OVER(PARTITION BY [Records].[SortField] ORDER BY [Records].[RunDetailID], [Records].[SortField], [Records].[PieceID]) AS VARCHAR), 4) NewSeq9
        FROM
        (
            SELECT
                [MRDFStorageID], 
                [RunDetailID], 
                [SortField], 
                [PieceID], 
                [Seq9], 
                [BallotType], 
                CAST([SpecialProcessing] as Int) StartCount
            FROM
                [MRDFStorage]
                    JOIN [BallotStyles] ON [MRDFStorage].[SortField] = [BallotStyles].[Style] and [BallotStyles].[JobDetailID] = @JobDetailID
            WHERE
                [RunDetailID] IN (SELECT [RunDetailID] FROM [RunDetails] WHERE [JobDetailID] = @JobDetailID AND [RunStatusID] <> 0)
        ) Records
    ) OrderedRecords 
        JOIN MRDFStorage ON [OrderedRecords].[MRDFStorageID] = [MRDFStorage].[MRDFStorageID]
WHERE
    [MRDFStorage].[RunDetailID] = @RunDetailID

Starałem się skupić na tej części:

[Records].[SortField] + RIGHT('0000' + CAST(ROW_NUMBER() OVER(PARTITION BY [Records].[SortField] ORDER BY [Records].[RunDetailID], [Records].[SortField], [Records].[PieceID]) AS VARCHAR), 4) NewSeq9

Czy jest jakiś efekt uboczny, o którym nie wiem?

AKTUALIZACJA Z DEFINICJAMI TABELI

CREATE TABLE [dbo].[MRDFStorage] (
    [MRDFStorageID]           INT            IDENTITY (1, 1) NOT NULL,
    [RunDetailID]             INT            NOT NULL,
    [PieceID]                 VARCHAR (15)   NULL,
    [SortField]               VARCHAR (20)   NULL,
    [BallotType]              VARCHAR (100)  NULL,
    [Seq9]                    VARCHAR (15)   NULL,
    CONSTRAINT [PK_MRDFStorage] PRIMARY KEY CLUSTERED ([MRDFStorageID] ASC),
    CONSTRAINT [FK_MRDFStorage_RunDetails] FOREIGN KEY ([RunDetailID]) REFERENCES [dbo].[RunDetails] ([RunDetailID])
);

CREATE TABLE [dbo].[BallotStyles] (
    [BallotStyleID]     INT           IDENTITY (1, 1) NOT NULL,
    [JobDetailID]       INT           NOT NULL,
    [Style]             VARCHAR (20)  NOT NULL,
    CONSTRAINT [PK_BallotStyles] PRIMARY KEY CLUSTERED ([BallotStyleID] ASC)
);

CREATE TABLE [dbo].[RunDetails] (
    [RunDetailID]        INT            IDENTITY (1, 1) NOT NULL,
    [JobDetailID]        INT            NOT NULL,
    CONSTRAINT [PK_RunDetails] PRIMARY KEY CLUSTERED ([RunDetailID] ASC)
);

Brak dostępu do danych sprawia, że ​​jest to trochę trudniejsze, ale udało mi się odtworzyć to, co widzisz, jeśli widzisz to:

insert into RunDetails(RunDetailID, JobDetailID) values(448, 1)
insert into MRDFStorage(RunDetailID, SortField) values(448, 'S1')
insert into MRDFStorage(RunDetailID, SortField) values(448, 'S1')
insert into MRDFStorage(RunDetailID, SortField) values(448, 'S1')
insert into BallotStyles(JobDetailID, Style) values(1, 'S1')
insert into BallotStyles(JobDetailID, Style) values(1, 'S1')

Kluczowym czynnikiem są tutaj dwa rzędy BallotStyles.

Dołączasz do BallotStylesnajbardziej wewnętrznego zapytania, a przy danych takich jak powyżej otrzymasz duplikaty wierszy dla każdego wiersza w MRDFStorage. Ponieważ nie używasz żadnej z kolumn BallotStyles, sprawdzasz tylko, czy istnieją wiersze, i można to zrobić za pomocą existsklauzuli, a to oczywiście nie utworzy duplikatów wierszy.

UPDATE
    [MRDFStorage]
SET
    [Seq9] = [OrderedRecords].[NewSeq9]
FROM
    (
        SELECT
            MRDFStorageID,
            [Records].[SortField] + RIGHT('0000' + CAST(ROW_NUMBER() OVER(PARTITION BY [Records].[SortField] ORDER BY [Records].[RunDetailID], [Records].[SortField], [Records].[PieceID]) AS VARCHAR), 4) NewSeq9
        FROM
        (
            SELECT
                M.[MRDFStorageID], 
                M.[RunDetailID], 
                M.[SortField], 
                M.[PieceID], 
                M.[BallotType]
            FROM
                [MRDFStorage] as M
-- Remove this join
--                    JOIN [BallotStyles] ON M.[SortField] = [BallotStyles].[Style] and [BallotStyles].[JobDetailID] = @JobDetailID
            WHERE
                [RunDetailID] IN (SELECT [RunDetailID] FROM [RunDetails] WHERE [JobDetailID] = @JobDetailID) and
-- Add this exists check instead of the join
                EXISTS (
                       SELECT *
                       FROM BallotStyles AS BS
                       WHERE M.SortField = BS.Style and
                             BS.JobDetailID = @JobDetailID
                       )
        ) Records
    ) OrderedRecords 
        JOIN MRDFStorage ON [OrderedRecords].[MRDFStorageID] = [MRDFStorage].[MRDFStorageID]
WHERE
    [MRDFStorage].[RunDetailID] = @RunDetailID