I've written a SQL Server query that updates records to have a sequential number after partitioning on a field. When I run it as a SELECT statement, everything looks great:
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
However, when I make the query into an UPDATE command, it begins skipping even numbers:
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
I've tried specifically focusing on this portion:
[Records].[SortField] + RIGHT('0000' + CAST(ROW_NUMBER() OVER(PARTITION BY [Records].[SortField] ORDER BY [Records].[RunDetailID], [Records].[SortField], [Records].[PieceID]) AS VARCHAR), 4) NewSeq9
Is there some side effect I'm ignorant of?
UPDATE WITH TABLE DEFINITIONS
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)
);
Best Answer
Not seeing the data you have makes this a bit harder but I managed to reproduce what you see if you see with this:
The key factor here is the two rows in
BallotStyles
.You are joining to
BallotStyles
in the inner most query and with data like above you will get duplicate rows for each row inMRDFStorage
. Since you are not using any of the columns inBallotStyles
you are only checking for existence of rows and that can be done with aexists
clause instead and that will of course not create the duplicate rows.