os: centos 7.6
db: oracle 19.3

Preparation data

Create table

-- drop table tmp_t0;
create table tmp_t0
(
  id   VARCHAR2(100),
  name VARCHAR2(100),
  memo VARCHAR2(100)
)
;

-- drop table tmp_t1;
create table tmp_t1
(
  id   VARCHAR2(100),
  name VARCHAR2(100)
)
;

insert data

insert into tmp_t0 values('1','a','a');
insert into tmp_t0 values('2','b','b');
insert into tmp_t0 values('3','c','c');

insert into tmp_t1 values('1','a');
insert into tmp_t1 values('2','b');
insert into tmp_t1 values('4','d');

commit;

SQL> set lines 200;
set pages 200;

col id format a5;
col name format a5;
col memo format a5;

SQL> select * from tmp_t0;

ID    NAME  MEMO
----- ----- -----
1     a     a
2     b     b
3     c     c

SQL> select * from tmp_t1;

ID    NAME
----- -----
1     a
2     b
4     d

update style 1

The simplest update

SQL> update tmp_t0 t0
   set t0.memo = t0.name || t0.name
 where 1=1
   and t0.id='1'
;

SQL> select * from tmp_t0;

ID    NAME  MEMO
----- ----- -----
1     a     aa
2     b     b
3     c     c

SQL> rollback;

SQL> select * from tmp_t0;

ID    NAME  MEMO
----- ----- -----
1     a     a
2     b     b
3     c     c

update style 2

Relevant update

SQL> update tmp_t0 t0
   set t0.memo = ( select t1.name ||'_'|| t1.name 
                     from tmp_t1 t1 
                    where t0.id = t1.id ) 
 where 1=1
;

SQL> select * from tmp_t0;

ID    NAME  MEMO
----- ----- -----
1     a     a_a
2     b     b_b
3     c

There is a phenomenon that the memo column with id=3 is updated to NULL and only the associated values are expected to be updated. As a result, even the unrelated ones are updated to NULL.

SQL> rollback;

SQL> select * from tmp_t0;

ID    NAME  MEMO
----- ----- -----
1     a     a
2     b     b
3     c     c

Add a condition to style 2

SQL> update tmp_t0 t0
   set t0.memo = ( select t1.name ||'_'|| t1.name 
                     from tmp_t1 t1 
                    where t0.id = t1.id and t0.id='1' ) 
 where 1=1
;

SQL> select * from tmp_t0;

ID    NAME  MEMO
----- ----- -----
1     a     a_a
2     b
3     c

Interestingly, in fact, the number of rows of update is limited by the lowest where.

SQL> rollback;

SQL> select * from tmp_t0;

ID    NAME  MEMO
----- ----- -----
1     a     a
2     b     b
3     c     c

update style 3

SQL> update tmp_t0 t0
   set t0.memo = ( select t1.name ||'_'|| t1.name 
                    from tmp_t1 t1 where t0.id = t1.id ) 
 where 1=1
   and exists ( select t1.name ||'_'|| t1.name 
                  from tmp_t1 t1 where t0.id = t1.id )
;

SQL> select * from tmp_t0;

ID    NAME  MEMO
----- ----- -----
1     a     a_a
2     b     b_b
3     c     c

Or use in

SQL> update tmp_t0 t0
   set t0.memo = ( select t1.name ||'_'|| t1.name 
                     from tmp_t1 t1 where t0.id = t1.id ) 
 where 1=1
   and t0.id in ( select t1.id 
                    from tmp_t1 t1 where t0.id = t1.id )
;

SQL> select * from tmp_t0;

ID    NAME  MEMO
----- ----- -----
1     a     a_a
2     b     b_b
3     c     c

The result is expected.

SQL> rollback;

SQL> select * from tmp_t0;

ID    NAME  MEMO
----- ----- -----
1     a     a
2     b     b
3     c     c

update style 4

It feels like merge should be the easiest thing to use now.

merge into tmp_t0 t0 using (
   select *
     from tmp_t1
) t1
on (
   t0.id = t1.id
)
when matched then
  update set t0.memo = t1.name ||'_'|| t1.name
/*  
when not matched then
  insert (t0.id,t0.name,t0.memo)
  values (t1.id,t1.name,'merge')
*/
;

SQL> select * from tmp_t0;

ID    NAME  MEMO
----- ----- -----
1     a     a_a
2     b     b_b
3     c     c
  

Reference resources:
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/MERGE.html#GUID-5692CCB7-24D9-4C0E-81A7-A22436DC968F