data.sql

CREATE TABLE t_b (id INT PRIMARY KEY, timestamp_event INT);
CREATE TABLE t_a (id INT PRIMARY KEY, id_latest_b INT REFERENCES t_b(id));
CREATE TABLE t_a2b (id_a REFERENCES t_a(id), id_b REFERENCES t_b(id), PRIMARY KEY (id_a, id_b));
INSERT INTO t_a VALUES (1, NULL);
INSERT INTO t_a VALUES (2, NULL);
INSERT INTO t_a VALUES (3, NULL);
INSERT INTO t_a VALUES (4, NULL);
INSERT INTO t_b VALUES (1, 10);
INSERT INTO t_b VALUES (2, 20);
INSERT INTO t_b VALUES (3, 30);
INSERT INTO t_a2b VALUES (1, 1);
INSERT INTO t_a2b VALUES (1, 2);
INSERT INTO t_a2b VALUES (2, 2);
INSERT INTO t_a2b VALUES (3, 1);
INSERT INTO t_a2b VALUES (3, 2);
INSERT INTO t_a2b VALUES (3, 3);
INSERT INTO t_a2b VALUES (4, 1);
INSERT INTO t_a2b VALUES (4, 4);

update.sql

UPDATE t_a
	SET id_latest_b = (
		SELECT id_b FROM (
			SELECT t_b.id as id_b, 
				   max(t_b.timestamp_event),
				   t_a2b.id_a
				   FROM t_b
				   JOIN t_a2b
					   ON (t_b.id = t_a2b.id_b)
				   GROUP BY t_a2b.id_a
		) q_inner
		WHERE 
		t_a.id = q_inner.id_a
	);