By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE v (
contract_id INT NOT NULL PRIMARY KEY,
tenant_id INT NOT NULL
);
CREATE TABLE t (
t_id INT NOT NULL PRIMARY KEY,
-- tenant_id column is missing here
account_no INT NOT NULL,
contract_id INT NOT NULL REFERENCES v
);
CREATE TABLE u (
u_id INT NOT NULL PRIMARY KEY,
tenant_id INT NOT NULL,
account_no_old INT NOT NULL,
account_no_new INT NOT NULL,
UNIQUE (tenant_id, account_no_old)
);
MERGE INTO (SELECT (SELECT t.account_no FROM dual) AS account_no_temp,
t.account_no, t.contract_id
FROM t) t
USING (
SELECT u.account_no_old, u.account_no_new, v.contract_id
FROM u, v
WHERE v.tenant_id = u.tenant_id
) s
ON (t.account_no_temp = s.account_no_old AND t.contract_id = s.contract_id)
WHEN MATCHED THEN UPDATE SET t.account_no = s.account_no_new;