為什麼我的 SQL Server 查詢在 UPDATE 上的行為與在 SELECT 上的行為不同？

我編寫了一個 SQL Server 查詢，該查詢在對欄位進行分區後更新記錄以具有序列號。當我將它作為 SELECT 語句執行時，一切看起來都很棒：

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

但是，當我將查詢變成 UPDATE 命令時，它開始跳過偶數：

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

我已經嘗試專門關注這部分：

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

有什麼我不知道的副作用嗎？

使用表定義更新

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)
);

沒有看到您擁有的數據使這有點困難，但我設法重現了您看到的內容：

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')

這裡的關鍵因素是BallotStyles.

您正在加入到BallotStyles最裡面的查詢中，並且使用上面的數據，您將獲得MRDFStorage. 由於您沒有使用其中的任何列，因此BallotStyles您只是檢查是否存在行，並且可以使用exists子句代替，這當然不會創建重複的行。

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

引用自：https://dba.stackexchange.com/questions/94820