Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table spot (spot_name varchar2(10), spot_id number);
create table tag_mapping_spot (spot_id number, tag_id number);
create table def_table (tag_ja varchar2(10), tag_id number);
insert into spot values('Name1', 1);
1 rows affected

insert into spot values('Name1', 1);
1 rows affected

insert into spot values('Name2', 2);
1 rows affected

insert into spot values('Name3', 3);
1 rows affected

insert into tag_mapping_spot values(1, 5);
1 rows affected

insert into tag_mapping_spot values(1, 5);
1 rows affected

insert into tag_mapping_spot values(2, 10);
1 rows affected

insert into def_table values('Name_new', 5);
1 rows affected

insert into def_table values('Name_new', 5);
1 rows affected

insert into def_table values('No_Name', 10);
1 rows affected

SELECT * FROM SPOT;


SPOT_NAME
SPOT_ID


Name1
1

Name1
1

Name2
2

Name3
3


UPDATE spot s
SET s.spot_name = (SELECT max(def_table.tag_ja) as f2
                      FROM def_table
                      INNER JOIN tag_mapping_spot
                      ON tag_mapping_spot.tag_id = def_table.tag_id
                      WHERE tag_mapping_spot.SPOT_ID = s.SPOT_ID)
where s.spot_id in (select spot_id from tag_mapping_spot );
3 rows affected

SELECT * FROM SPOT;


SPOT_NAME
SPOT_ID


Name_new
1

Name_new
1

No_Name
2

Name3
3