How to write the result of a selection with inner join to a table column?

Question

Oracle

Hello, I already asked a similar question when I wanted to transfer data from one table to another using update, then it gave such an error

spoiler

Error starting at line : 1 in command -
update contract_city
set manager_id =
(select contract_manager.manager_id
from contract_manager
where contract_manager.contract_id = contract_city.contract_id)
Error report -
ORA-01427: подзапрос одиночной строки возвращает более одной строки

with this request:

spoiler

update contract_city
set manager_id = 
(select manager_id
from contract_manager
where contract_manager.contract_id = contract_city.contract_id)

I read the documentation, found what I need, https://oracleplsql.ru/joins.html
But again, the problem arose, if you simply pull out a column in such a way, then everything is fine, the manager_id column is pulled from the contract_manager table with all values.

(select contract_manager.manager_id       
from contract_manager
inner join contract_city
on contract_manager.contract_id = contract_city.contract_id)

but if you use

update contract_city
set manager_id = 
(select contract_manager.manager_id       
from contract_manager
inner join contract_city
on contract_manager.contract_id = contract_city.contract_id)

Same error. Do you know how you can write the column that was pulled out using join into the manager_id column of the same name, the contract_city table, or something else.

Answer 1

use merge and rowid
merge into target t
using
( with update_list as
(select t.rowid rid, sn, ts, ss should_be_s
from target t, source s
where tn = sn
and ss != ts)
select u.* from update_list u
) x
on (t.rowid = x.rid)
when matched
then
update set ts = x.should_be_s