I have a trigger in postgresql that updates after insert and is taking the value to be updated with a subquery, like this:

CREATE OR REPLACE FUNCTION UPDATE_EVERGREEN_FROM_LOG() RETURNS TRIGGER AS $$
begin

update evergreen set
intended_remediation_date = log_entry.intended_remediation_date,
helios = log_entry.helios,
remediation_type = log_entry.remediation_type,
remark = log_entry.remark,
admin_remark = log_entry.admin_remark,
modified = log_entry.modified,
modify_by = log_entry.modify_by
from
(
select
    intended_remediation_date,
    helios,
    remediation_type,
    remark,
    admin_remark,
    modified,
    modify_by
    from deleted_evergreen_log
    order by dml_timestamp desc
    limit 1
) as log_entry
where host_id = new.host_id
and binary_id = new.binary_id
and it_service = new.it_service;
return new;
end;
$$ LANGUAGE PLPGSQL;


CREATE OR REPLACE TRIGGER AFTER_INSERT_EVERGREEN AFTER
INSERT ON EVERGREEN
FOR EACH ROW EXECUTE FUNCTION UPDATE_EVERGREEN_FROM_LOG();

the problem is that

select
    intended_remediation_date,
    helios,
    remediation_type,
    remark,
    admin_remark,
    modified,
    modify_by
    from deleted_evergreen_log
    order by dml_timestamp desc
    limit 1

will at most select a single value from the deleted_evergreen_log table, I want actually to update
with different values where this part of the sql query:

where host_id = new.host_id
and binary_id = new.binary_id
and it_service = new.it_service

match. So for example if there are records in deleted_evergreen_log like this:

host_id binary_id it_service dml_timestamp  intended_remedation_date
1              1                    1                  2024-01-01           2090-01-01
1              1                    1                 2024-02-02            2030-01-01
2              2                    2                 2024-01-01           2090-01-01
2              2                    2                 2024-02-02           2040-01-02

then what should be updated is only these records:
1              1                    1                 2024-02-02            2030-01-01
2              2                    2                 2024-02-02           2040-01-02

this is because they have their latest timestamp, despite having the same host_id binary_id it_service