SQL Server 2017

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

create table tabela1 (campo1 int, campo2 date, campo3 varchar(1));
create table tabela2 (campo1 int, campo2 date, campo3 varchar(1));

insert into tabela1 (campo1, campo2, campo3) values
(959,          '2023-03-17',      'G'),
(959,          '2023-03-18',      'G'),
(959,          '2023-03-19',      'G'),
(970,          '2023-03-15',      'G'),
(970,          '2023-03-17',      'G');

insert into tabela2 (campo1, campo2, campo3) values
(959,          '2023-03-17',      'G'),
(959,          '2023-03-18',      'G'),
(959,          '2023-03-19',      'G'),
(970,          '2023-03-15',      'G'),
(970,          '2023-03-17',      'G');

select 'antes do resultado' ;

--update tabela2 where 
select campo1, campo2, campo3 from tabela1;
select  campo1, campo2, campo3 from tabela2;
--select campo1, MAX(campo2) from tabela1 as tmp group by campo1;

Select  Campo1, Max(campo2) as UltimaData into #tmp From Tabela1 Group By Campo1;
select * from #tmp

select row_number() over (order by campo1, ultimadata) linha, campo1, ultimadata into #tmpTabela from #tmp
select 'registros selecionados à serem alterados'
select * from #tmpTabela;

declare @linha int;
set @linha = 1;

declare @campo1Var int;
declare @ultimaDataVar Date;

while exists (select * from #tmpTabela)
begin

set @campo1Var = (select top 1 campo1 from #tmpTabela where linha = @linha order by linha asc);
    set @ultimaDataVar = (select top 1 ultimadata from #tmpTabela where linha = @linha order by linha asc);
    
    update tabela2 set campo3 = 'R' where campo1 = @campo1Var and campo2 = @ultimaDataVar;

delete from #tmpTabela where linha = @linha;
    set @linha = @linha +1 ;
end

select 'depois do resultado' ;
select * from tabela1;
select * from tabela2;

drop table #tmptabela;

(No column name)
antes do resultado

campo1	campo2	campo3
959	2023-03-17	G
959	2023-03-18	G
959	2023-03-19	G
970	2023-03-15	G
970	2023-03-17	G

campo1	campo2	campo3
959	2023-03-17	G
959	2023-03-18	G
959	2023-03-19	G
970	2023-03-15	G
970	2023-03-17	G

Campo1	UltimaData
959	2023-03-19
970	2023-03-17

(No column name)
registros selecionados à serem alterados

linha	campo1	ultimadata
1	959	2023-03-19
2	970	2023-03-17

(No column name)
depois do resultado

campo1	campo2	campo3
959	2023-03-17	G
959	2023-03-18	G
959	2023-03-19	G
970	2023-03-15	G
970	2023-03-17	G

campo1	campo2	campo3
959	2023-03-17	G
959	2023-03-18	G
959	2023-03-19	R
970	2023-03-15	G
970	2023-03-17	R