SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tablename ([Id] int, [Version] varchar(4), [ItemNo] int, [Notes] varchar(11));
    
INSERT INTO tablename ([Id], [Version], [ItemNo], [Notes]) VALUES
    (1, NULL, 31, NULL),
    (2, '1', 31, 'tasty'),
    (3, '2', 31, 'kinda tasty'),
    (4, NULL, 32, NULL),
    (5, '1', 32, 'meh'),
    (6, '2', 32, 'alright'),
    (7, '3', 32, 'fabulous'),
    (8, NULL, 33, 'ambivalent'),
    (9, '1', 33, 'gross'),
    (10, '2', 33, 'puke');
10 rows affected

WITH cte AS (
  SELECT *, FIRST_VALUE(Notes) OVER (PARTITION BY ItemNo ORDER BY Version DESC) newNotes 
  FROM tablename
)
UPDATE cte
SET Notes = newNotes
WHERE Version IS NULL AND Notes IS NULL;
2 rows affected

SELECT * FROM tablename;


Id
Version
ItemNo
Notes


1
null
31
kinda tasty

2
1
31
tasty

3
2
31
kinda tasty

4
null
32
fabulous

5
1
32
meh

6
2
32
alright

7
3
32
fabulous

8
null
33
ambivalent

9
1
33
gross

10
2
33
puke

Id	Version	ItemNo	Notes
1	null	31	kinda tasty
2	1	31	tasty
3	2	31	kinda tasty
4	null	32	fabulous
5	1	32	meh
6	2	32	alright
7	3	32	fabulous
8	null	33	ambivalent
9	1	33	gross
10	2	33	puke