Your data does not have a sequence defined. Without a column to order by, there is no defined order for your rows, so there is no way to know which rows should appear between "abc" and "endabc".
Add a column to provide a sequence to your table:
CREATE TABLE Test3
(
ID int NOT NULL IDENTITY(1, 1),
name nvarchar(255) NOT NULL,
SecondColumn nvarchar(255) NULL
);
INSERT INTO Test3 (name)
VALUES
('abc'),
('123abc456'),
(''),
('acode'),
('endabc'),
('def'),
('enddef')
;
You can then use a sub-query to find the name group for each row:
SELECT
ID,
name,
(
SELECT TOP 1 Substring(name, 4, Len(name))
FROM Test3 As T2
WHERE T2.ID >= T.ID
And T2.name Like 'end%'
ORDER BY T2.ID
) As NameGroup
FROM
Test3 As T
;
You can then use that to update your table:
UPDATE
T
SET
SecondColumn = (
SELECT TOP 1 Substring(name, 4, Len(name))
FROM Test3 As T2
WHERE T2.ID >= T.ID
And T2.name Like 'end%'
ORDER BY T2.ID
)
FROM
Test3 As T
;