So something like this (run in an empty test database):

--Example table

create table dbo.Labels

(Id int identity (1,1),

Value varchar(20),

IntSequence bigint,

ExternalSequence bigint,

BatchId int)

-- Insert some data

Insert into dbo.Labels

(Value, IntSequence, ExternalSequence, BatchId)

Values

--batch 1

('exampleValue', 1,100001,1),

('exampleValue', 2,100002,1),

('exampleValue', 3,100003,1),

--batch 2

('exampleValue', 4,100006,2),

('exampleValue', 5,100007,2),

('exampleValue', 6,100008,2),

-- batch 3

('exampleValue', 7,100010,3),

('exampleValue', 8,100011,3),

-- Insert the missing values for batch 1

('exampleValue', 9,100004,1),

('exampleValue', 10,100005,1),

-- insert the missing values for batch 2

('exampleValue', 11,100009,2)

select * from dbo.Labels order by IntSequence asc; -- Current order showing external seq in wrong order, but internal seq in correct order.

select * from dbo.Labels order by ExternalSequence asc; -- Order by the other column which is in correct order shows true order, but internal sequence in wrong order

with basedata as (

select

Value,

IntSequence,

ExternalSequence,

BatchId,

rn = row_number() over (order by BatchId, ExternalSequence)

from

dbo.Labels

)

update basedata set

IntSequence = rn;

select * from dbo.Labels order by IntSequence; -- Current order showing external seq in wrong order, but internal seq in correct order.

select * from dbo.Labels order by ExternalSequence asc -- Order by the other column which is in correct order shows true order, but internal sequence in wrong order

drop table dbo.Labels;